Solved

How to get data types of fields in Generic Query in Code without using DACS

  • 29 November 2022
  • 7 replies
  • 210 views
E
Rank
Userlevel 3
Badge

I have already gotten the data using the following loop.  It gets the data just fine without using DACs.  But when I try to determine if the datatype of the column in the GenericQuery is string or numeric, I seem to only find System.String or String.  I don’t get the datatype of the underlying data element in the generic query - like refnbr is nvarch(15) or something like that.  Anyone have any thoughts? 

 

foreach (GenericResult resultRow in graph.Views["Results"].SelectMulti())
            {
                
                foreach (string key in resultRow.Values.Keys)
                {
                    
                    foreach (GIResult resultMap in PXSelectReadonly<GIResult, Where<GIResult.designID, Equal<Required<GIResult.designID>>, And<GIResult.objectName, Equal<Required<GIResult.objectName>>>>>.Select(graph, new object[] { templates.Current.DesignID.Value, key }))
                    {
                        
                        var result = graph.Caches[resultRow.Values[key].GetType()].GetValue(resultRow.Values[key], resultMap.Field);
                        
                        sLine = sLine + "\"" + result + "\"" + resultMap.Field.GetType().ToString() + resultMap.Field.GetTypeCode();
                    }
                }
            }

icon

Best answer by Leonardo Justiniano 1 December 2022, 18:18

View original

7 replies

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi @edwardmcgovern97 

I understand that GIs are modifiable by users and you are not certain about which objects you are going to encounter. I prefer to work with known entities. 

Having said that, I just got this snipped code working for you which extract the property types of the first entity wrapped in a PXResult object:

// Assuming PXResultset<GenericResult> results
foreach (GenericResult resultRow in results)
{                
...
	// Having the DAC CLASS. MY PREFERENCE !
	DAC entity = resultRow.Values["DAC"];
...

	// Getting DACs using reflection
	// Example: First DAC of a GI Result
	// Inspect the first entity wrapped into PXResult (ElementAt(0))
	foreach (PropertyInfo prop in resultRow.Values.ElementAt(0).Value.GetType().GetProperties())
	{
		Type tprop = prop.PropertyType;
		if (tprop.IsGenericType && tprop.GetGenericTypeDefinition() == typeof(Nullable<>))
		{
			tprop = tprop.GetGenericArguments()[0];
		}
		PXTrace.WriteInformation(tprop.FullName); 
	}	
...
}

Hope this helps

Leo
E
Rank
Userlevel 3
Badge

Thanks but not what I’m looking for. DAC is not an option, have to use generic query and its written to work with any generic query, not hard coded for one. Thats why DAC is not an option. 

 

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Thanks but not what I’m looking for. DAC is not an option, have to use generic query and its written to work with any generic query, not hard coded for one. Thats why DAC is not an option. 

 

Hi @edwardmcgovern97 

Please pay attention to the second part. That gets the data types of each property (first entity on the example)

 

Leo
E
Rank
Userlevel 3
Badge

Oh, I’m sorry,  I thought it was all part of the same loop.  I will try

 

E
Rank
Userlevel 3
Badge

I tried and I realized I didn’t mention one thing, I am only working with the fields in the result set (fields displayed in the result grid of the generic query) 

I think your loop is pulling all fields from any table in the query 

and the order doesn’t seem to match the order of the values that I am pulling probably related to the same issue. 

 

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

I think your loop is pulling all fields from any table in the query 

Yes Indeed.

To only get the visible columns of the target GI you need to query its definition first. This an improved snipped:

// GET VISIBLE Fields

var qGI = new SelectFrom<GIResult>.InnerJoin<GIDesign>.On<GIResult.designID.IsEqual<GIDesign.designID>>.
	Where<GIResult.isActive.IsEqual<True>.
		And<GIResult.isVisible.IsEqual<True>>.
		And<Use<GIDesign.name>.AsString.IsEqual<@P.AsString>>>.
	View(this);

List<Tuple<string,string>> fields = new List<Tuple<string, string>>();
fields.AddRange(qGI.Select("<YOUR GI>").RowCast<GIResult>().Select(s => new Tuple<string,string>(s.ObjectName, char.ToUpper(s.Field[0]) + s.Field.Substring(1))).ToList());

The code would be like:



// Assuming PXResultset<GenericResult> results
foreach (GenericResult resultRow in results)
{                
...

	// Getting DACs using reflection
	// for each resultRow in Results, I inspect all entities wrapped into PXResult
	foreach (var entity in resultRow.Values)
	{
		foreach (PropertyInfo prop in entity.Value.GetType().GetProperties())
		{                    
            // fields contains the entity and the visible field
			if (fields.Any(a => a.Item1 == entity.Key && a.Item2 == prop.Name))
			{
				Type tprop = prop.PropertyType;
				if (tprop.IsGenericType && tprop.GetGenericTypeDefinition() == typeof(Nullable<>))
				{
					tprop = tprop.GetGenericArguments()[0];
				}
				PXTrace.WriteInformation(entity.Key + "." + prop.Name + " : " + tprop.FullName);
			}
		}
	}

...
}

 

Leo
E
Rank
Userlevel 3
Badge

Thanks that works and I learned a few things here.  Thanks

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved