Reading GI Values from Graph Code 2024r1

Hi ​@Dantheman88988,

Please try below code snippet to check whether it is returning values successfully or not.
 

PXGenericInqGrph gi = PXGenericInqGrph.CreateInstance("GI640596", "KitBuild3");
PXResultset<GenericResult> results = gi.Results.Select();

string test = "";

foreach (GenericResult resultRow in results)
{
    foreach (KeyValuePair<string, object> kvp in resultRow.Values)
    {
        if (kvp.Value is GenericResult nestedResult)
        {
            // Iterate over nested GenericResult values
            foreach (KeyValuePair<string, object> nestedKvp in nestedResult.Values)
            {
                if (nestedKvp.Key.ToString() == "KitQty") // Replace with actual field name
                {
                    test += "\n" + nestedKvp.Value?.ToString();
                }
            }
        }
    }
}

Hope, it helps!

Thanks Dipak,

The test string did not return anything. Then I commented out your if statement as follows:

 PXGenericInqGrph gi = PXGenericInqGrph.CreateInstance("GI640596", "KitBuild3");
 PXResultset<GenericResult> results = gi.Results.Select();

string test = "";

                foreach (GenericResult resultRow in results)
                {
                    foreach (KeyValuePair<string, object> kvp in resultRow.Values)
                    {
                        if (kvp.Value is GenericResult nestedResult)
                        {
                            // Iterate over nested GenericResult values
                            foreach (KeyValuePair<string, object> nestedKvp in nestedResult.Values)
                            {
                                //if (nestedKvp.Key.ToString() == "KitQty") // Replace with actual field name
                                //{
                                    test += "\n" + nestedKvp.Value?.ToString();
                                //}
                            }
                        }
                    }
                }

and the string now looks like:

PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]
PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]
PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]
PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]
PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]
PX.Objects.IN.INKitSpecStkDet
PX.Data.GenericResult
PX.Objects.IN.INSiteStatus
System.Collections.Generic.Dictionary`2[System.String,System.Object]

Is there another layer of nesting?

​@Dantheman88988 , try this code sample
This code gets the values ​​from the current GI View and stores them in a structure List<HashSet<ValueTuple<string, string, object>>>

protected virtual List<HashSet<ValueTuple<string, string, object>>> GetScreenData()
{
	var graph = Base;

	var gridData = graph.Views["Results"]
						.SelectMulti()
						.Cast<GenericResult>()
						.Select(r => r.Values);

	var filter = graph.Views["Filter"].Cache.Current;
	var columns = graph.ResultColumns;
	var resultTable = new List<HashSet<ValueTuple<string, string, object>>>();

	foreach (var data in gridData)
	{
		var rowData = new HashSet<ValueTuple<string, string, object>>();
		foreach (var column in columns)
		{
			var table = data[column.ObjectName];
			var cache = graph.Caches[column.ObjectName];

			var value = cache.GetValue(table, column.Field);
			if (value != null && rowData.All(r => r.Item1 != column.FieldName ))
			{
				//value = TryGetColumnDisplayName(cache, item, column, value);
				var columnDisplayName = TryGetColumnDisplayName(table.GetType(), column);
				rowData.Add((column.FieldName, columnDisplayName, value));
			}
		}
		if (rowData.Count > 0)
			resultTable.Add(rowData);
	}

	return resultTable;
}

line var value = cache.GetValue(table, column.Field); returns value for cell.
I think it won't be difficult for you to modify this code to suit your task.

Could you help me how would i adapt this code to extract the data from a GI without it being open? My code is being updated on FIeldUpdated event from the Sales Order screen so I cant have the GI open..

Out of curiosity - what is the benefit of using the GI as the data source vs a BQL query in this case?

It's comprised of 2 underlying GIs..

KitBuild1 aggregates the kit spec detail table to find the highest revision kit spec.

KitBuild2 then joins the above tables again with the kit detail table and INSiteStatus to determine qtyavail for the components and qty required for the kit. I then divide these two fields.

Kitbuild3 then takes table above and aggregates it, choosing the minimum of the division above which in turn determines the maximum quantity of a kit that could be built based on its components.

Could this be done in BQL, I have no idea how to use BQL. Can you define sub tables in BQL. I need to avoid using SQL views as these are being discouraged/removed in Australia

BQL very much is like SQL but it’s structured so that the underlying database layer can be given specific instructions.

You can have sub-selects within BQL. But it might be tricky to completely convert your view into a single query - it all depends on the SQL. Fluent BQL is a little easier to keep track of, I find.

There is an advantage to running the queries within your code and not having to create the “perfect” database view because you can be smarter about querying the database.

Hi Dantheman88988,

What makes you say “I need to avoid using SQL views as these are being discouraged/removed in Australia”?  I have lots of customisations out there that use views and a system generated DAC to accomplish the sort of thing you’re looking to achieve here.

Thanks,

John

Also, I sometimes use a combination of Microsoft C# LINQ and BQL when things get a bit complex.  The below is an example of something I would personally struggle to do in BQL alone.  I’m sure it can be done - it would just take me more time to do than I want to invest right now.

LINQ is just what I’m used to as I use it all the time in my non-Acumatica work.  I find it easier to write vs FluentBQL.  (a lot of this is due to a lack of practice on the BQL side on my part).  There are some things that LINQ wont do within Acumatica.  For instance, I haven’t been able to get joins between  custom fields and standard fields with different names to work in LINQ. 

I think the main thing with this approach is to be mindful of  the difference between IQueryable and IList.  The idea is to make sure you’re not smashing the database by retrieving more data than you need and then subsequently cutting it down to what you really want.  Whilst you are operating on an IQueryable object you are just constructing the query - nothing is happening on the database.  When you execute the query (using .ToList()), the database runs the SQL and returns the data.

For example the above returns  MASCCommissionDetail data records and then summarizes it locally.  Wouldn’t be the best option if I was just after the summarized data.  In the instance above though I needed to update all the MASCCommissionDetail records later in the process.

Also, Chat GP is surprisingly useful at speeding up the writing of Fluent BQL (even for reasonably complex SQL statements). Use “convert {your sql statement} to fluent BQL using PX.Data.BQL.Fluent”.  You just have to watch out for N+1 queries.  As an example:

This gave:

Which is OK until you get to the N+1 queries highlighted.

When you start wanting to return data from a mix of tables into a single record set you probably are best to use a projection (if you want to avoid using a view).

That’s my 2 cents worth on the subject.  Still would like to know the answer to my question above re the views.  I can cope with discouraged, but removed is a headache :(

John.