LinkedIn
Twitter
I am creating an expression as follows:
SELECT(Parts[Part ID], IN([Kit], SELECT([Related Ordered Kits][Kit], [Processed?] <> true)))
I am trying to leverage already existing REF_ROWS for better performance.
When using this in an action on Form Save, it doesnโt appear to return any rows. So I began testing with the aid of Virtual Column (VC).
The VC is defined as a List of Ref Type with the base table = โPartsโ. Part ID is the Parts table key column.
In the Detail view, the VC shows as below named โTest Kit Countโ - indicates 4 rows but no rows are displayed
Clicking on the โViewโ button, the correct Parts_Inline view is being used but again no rows displayed. This same view is used elsewhere with a REF_ROWS column without issue so I donโt think it is a view problem.
If I change VC List base type from Ref to text, the 4 Part ID values are properly listed in the view.
However, if I click the Edit button to go into the Form and simply click Save, when the app returns to the Detail view the VC (base type as text) is blank initially. Waiting for few seconds, without touching anything else, the VC will update to the 4 values again as shown above.
What am I missing?
Why is the initial expression, when used in an action returning no rows?
Why, when placing the expression in a VC does it show no rows in the inline view?
And why, when the base type is text, does the VC blank out after a Save and then update again with the correct values?
If a child record of the table that has this marked as โTrueโ is updated, then a re-calc of the parent level is triggered - or itโs supposed to.
This level of integration is buggy.
I would never do anything like thisโฆ
I see what youโre trying to do, but you need to split things into different chunks.
For instance:
Take your de-reference list and make that itโs own column
[Related Order Kits][Kit]
But, since you want only the ones that are not processed - first, create a slice that represents these, then make your ref_rows use that instead of the root table.
This then leaves you with a list of all the [Related Incomplete Kits][Kit] in a single column - or in other words: โa list of all the [kit] values for each incomplete kit.โ
To get your actual result, a list of all the [Part ID]s for the kits that have yet to be processedโฆ
- If you create a slice for these Incomplete kits, you can easily pull data from that subset.
- Inside your Kit table, create a VC of all the remaining parts that need to be claimed (or whatever).
Then you can use the following Incomplete_Kits[Remaining_Parts] to pull a list of all the โlists of partsโ - which you need to specially deal with by using SPLIT() to transform that into a regular โlist of part IDs.โ
SPLIT(Incomplete_Kits[Remaining_Parts], " , ")
That will give you a LIVE list of all the remaining part IDs from all the records inside the slice.
When you see this, some piece of meta-data is off; typically it will be that at some point the โrefโ status of the data is lost.
- this might be due to the fact that youโre trying to do too much in one column.
If I were you: from here I do what Iโm suggesting and split things out into separate columns.
I usually find itโs better to put a dereference-list into itโs own column, then use that in whatever formula Iโm trying to get working.
I can completely understand where youโre coming from @WillowMobileSystems, and youโre thinking about things in the right way - now we need to add in a whole-nother layer: the calculation steps.
This is where the real meat and potatoes of your appโs sync time comes in.
If youโve got the following in a VC:
Select(Parts[PartID], in([PartID], Select([Related Records][Column_A], [Column_B] = โSome Valueโ)))
the following will happen:
-
First the list of related records needs to be created
- this is relatively fast, the system is design that way, but itโs still essentially another version of the following:
Select(ChildTable[ChildID], [Parent_Ref] = [_Thisrow].[ParentID])
- So for each parent record, a complete search through every child record happens to find the ones that should be in the list.
- Meaning: If Iโve got 10 parent records and 10 child records, then weโve got 100 records to parse through and sort into the appropriate lists
- More realistically, my parent table might have 500 rows and the child 1500
- 750,000 rows that your app is having to manageโฆjust right there.
- Thankfully, AppSheet engineers designed an indexing system in the background that indexes this sh!t out of your data for you - so while this sounds daunting, the system handles it really fast.
- So we can pretty much ignore this part of the steps (though they do still happen, theyโre just really really fast).
-
Now that the system has the base child lists, next we need to process your actual formula. The first part we need is the de-reference list of [Column_A] values - and since the de-ref list is included in a select formula, the additional conditions from that select are applied to this next step.
- Another select happensโฆ
Select(Child_Table[Column_A], and(
in([ChildID], [_Thisrow].[Related Records]),
[Column_B] = โSome Valueโ
))- How many child records were there again?? 1500 - so thatโs 1500 record-calucation/evaluation-steps just to find that portion of your formula - for one row.
- Since weโve got 500 parent records, thatโs 750,000 steps for the whole tableโฆ
- Now we have a list of all the [Column_A] values from the [Related Records] column, where [Column_B] = โSome value.โ
- The final step is to take that list, and then apply that to the Parts table. This is where the comes inโฆ
- How many records in the parts table???
Thatโs a bit muchโฆ letโs say 2000.
- For each record in the parts table, ALL of the previous steps happen.
For a grand total ofโฆ 2000 X 750,000
- 1,500,000,000 rows to parse.
Thatโs how many records the system is having to deal with, just in that one VC.
- If you dig deep into the audit logs, you can find the times for each and every one of these steps, and while many of them are times like 0.00235 they still add up.
I should note: Iโm not sayin that the system is having to make that many calculationsโฆ
what Iโm saying is that the 1.5 billion number is the RECORD COUNT of how many times the system will read data from your tables.
To my suggestion of the slice:
If you move the [Column_B] = โSome Valueโ into a slice, instead of that being calculated for each record, instead it only processes once - when the slices are calculated - then that list is stored in the slice.
To the point of moving the de-reference list into itโs own column:
- thatโs because itโs been my experience that the system works better when de-reference lists are put into their own column;
- this way they contain all the necessary meta data, that sometimes you wonโt be able to set due to too many layers
Hope I made some sense in there; this is a really complicated and dense topic. Iโm putting together a video now about variable handling, should be out in a few weeks.
@Fabian no 
Itโs been on my list this whole time, and Iโve actually tried a few times - but every time I try and come up with some sort of way to communicate all this cohesively in a videoโฆ it just doesnโt come across.
Itโs still on my list, thanks for the reminder - Iโll take another crack at it.
