Solved

Looping Generic Query to get list of values from fields shows only empty string "" for Columns not using fieldss

  • 23 January 2023
  • 3 replies
  • 170 views

Userlevel 3
Badge

I am using this loop to get the values of a generic inquiry.  the requirements it needs to loop all columns in the Inquiry in order and get the value of each column.  

The code below is working but only for fields in the Generic Inquiry that are based on an actual Field.  So if I have a field I put in the Caption “Field” and the formula = ‘Test’ 

it doesn’t bring back Test as I would expect, it brings back “”  - no value?  When I preview the data 

I see ‘Test’ but when pull the data using below I get ‘’

Any clue would be appreciated. 

 

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 edwardmcgovern97 27 January 2023, 11:28

View original

3 replies

Userlevel 3
Badge

OK after alot of poking around resultRow (Generic Result) has two levels to Values[0] and [1].  [0] has the table and [1] has the functions.   

when I do: 

resultRow.Values["GenericResult"]

I get this: 

Count = 7
    [0]: {[Sub_Formula11012a6665ef4bf4a1b51b4f82d9f1e5, Sub Account]}
    [1]: {[Sub_Formulaba86b6bea9ae4685834ad68dfd2d4bd2, 000-000]}
    [2]: {[Sub_Formulade5a4a5d6faa465c8627eca5b7accd11, All Sub Accounts]}
    [3]: {[Sub_Formulaf65989dabbe2410c8e9435012ccc83f2, +]}
    [4]: {[Sub_Formula7b4153dd71134ff596e96295263769e2, ]}
    [5]: {[Sub_Formula0f94a5f6c90a4f0690195af2c3bbfa07, ]}
    [6]: {[Sub_Formulabd8b2b2073014ebb8c5c476276a79ad4, ]}
Which is a list of all functions and what value is returned 

 if I do 

resultRow.Values["Sub"]

I Get: 

{PX.Objects.GL.Sub}
    Active: null
    ConsoSubCode: null
    ConsoSubID: null
    CreatedByID: null
    CreatedByScreenID: null
    CreatedDateTime: null
    Description: "Default"
    GroupMask: {byte[0]}
    Included: false
    LastModifiedByID: null
    LastModifiedByScreenID: null
    LastModifiedDateTime: null
    NoteID: {f29cb555-4129-44dd-9565-075eb0733e62}
    SubCD: "000000"
    SubID: 467
    _Active: null
    _CreatedByID: null
    _CreatedByScreenID: null
    _CreatedDateTime: null
    _Description: "Default"
    _GroupMask: {byte[0]}
    _Included: false
    _LastModifiedByID: null
    _LastModifiedByScreenID: null
    _LastModifiedDateTime: null
    _NoteID: {f29cb555-4129-44dd-9565-075eb0733e62}
    _SubCD: "000000"
    _SubID: 467
    _tstamp: null
    tstamp: null
 

I am using the Cache to get at the values and the field name returned for data from a table comes back as the field name which works. The formula fields come back as the formula itself so like a string with “=Substring(somefield, 1, 2) + ‘-’ + substring(somefield, 3, 4)”

as you can image Cache returns just “” 

Any ideas on how to navigate this.  The answer seems to be grab from resultRow.Values["Sub"]

when an actual data field and grab from resultRow.Values["GenericResult"] 

when a function

How to tell a function? FieldName has = in first character

Not sure how to pull from resultRow.Values["GenericResult"]

 

Userlevel 3
Badge

Ok I solved my problem by putting the resultRow.Values[“GenericResult”] in a Dictionary and counting how many functions I have read through the main recordset, identified by = as the first character.  Then I loop the dictionary for the same number and grab the value.  Seems to work, its a hack but it works. 

foreach (KeyValuePair<string, object> kvp in (Dictionary<string, object>)oformula)

where oformula is defined as: 

object oformula = (Dictionary<string, object>)resultRow.Values["GenericResult"];

 

Userlevel 7
Badge

Thank you for sharing your solution with the community @edwardmcgovern97 !

Reply


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