I have a Service Order Management app that users can place Estimates and then, when approved, convert to an Order. A Work Order object has child references for Products and Materials. When the Estimate is switched to an Order, a Workflow is activated to update Allocated counts in the Inventory table for the associated Products and Materials
I am getting a puzzling error when this Workflow is processed. When I have only a single record in these child references, the updates DO occur as expected in the Inventory table but I still get this error. If there are multiple child records, only the first record is processed and no others. I believe the error is preventing further processing.
What could be causing this error? What am I missing?
Please let me know if you need more information.
Below is the error I am seeing from the log (similar error for each Products and Materials Action sets):
Audit Log Details:
Properties:
{
โAppIdโ: โea11df8a-9df1-4f29-afea-d6300774751bโ,
โAppTemplateVersionโ: โ1.000143โ,
โRuleNameโ: โOrder Placedโ,
โEventTypeโ: โChangeโ,
โInvokedByโ: โUpdateโ,
โServerโ: โprodu991100005Tโ,
โIgnoreSecurityFiltersโ: false,
โTableNameโ: โWorkOrdersโ,
โRuleTableNameโ: โWorkOrdersโ,
โOperationUpdateModeโ: โUPDATES_ONLYโ,
โEventMatchโ: โWorkflow event successfully matchedโ,
โConditionโ: โ=AND([_THISROW_BEFORE].[Work Type] = โEstimateโ, [_THISROW_AFTER].[Work Type] = โOrderโ)โ,
โMatchesConditionโ: โTrueโ,
โActionResultsโ: โCreated 1 ActionResultsโ,
โAction Typeโ: โTakeActionโ,
โAction Nameโ: โUpdate Allocation for Materials Usedโ,
โErrorsโ: "Error: โAction on a set of rowsโ โUpdate Materials Allocationโ for table 'Materials failed with exception Value cannot be null.\r\nParameter name: source ",
โAppTemplateNameโ: โServiceInsightBase-526414โ,
โOperationโ: โWorkflow actionโ,
โResultโ: โFailureโ
}
Right. The parameter name says โsourceโ but I am not sure what that refers to.
The Action sequence is mildly complicated. Iโll try to explain it in shorthand below:
Action1 - Called from Workflow - Action on set of rows - Rows = โRelated Materialsโ - Action = Action 2
Action2 - Called from Action1 - Action on set of rows - Rows = FILTER(Inventory, =Product Code ) - Action = Action 3
Action 3 - Called from Action2 - Data Change - Allocated = SUM(Quantity in Material Rows =Product Code)
It is in Action 2 where the error is being reported - even after correct processing of the single or first row. I would assume that the โsourceโ here is the โRelated Materialsโ rows and it is definitely not null. Not sure why it thinks so.
Can I not chain Actions in this manner? Am I missing a more simplified way to achieve the same thing?
By the way this is not a deployed app, is there any testing limitations I might be hitting?
Thereโs something a miss here.
It seems like youโve got a trigger, the workflow, that initiates action 1โฆ
that initiates action 2โฆ
that finally does what you want.
Why the middle ground with action 2??
For each new Material record added, I am recalculating the Total Allocated amount and applying that to the Inventory record instead of trying to make increments/decrements as records are added or changed.
To do that, I first need to translate the Material record to the associated Inventory row based on Product Code. Then I can update that rows Quantity Allocated value.
I am starting with a Work Order:
Action1 - Translates processing to the List of Materials records on the Work Order - โRelated Materialsโ
Action2 - Finds the associated Inventory record by product code (always expect 1 row here)
Action3 - Updates the Inventory row based on SUM calculation.
I donโt think I can skip Action2 because I need that reference to the Inventory record in order to update it in Action 3.
Maybe I can combine Action1 and Action2? I really just need a reference to the list of Inventory rows that need updated - in this case based on the Materials list on the Work Order.
@MultiTech_Visions Ok, I did combine Action1 and Action2 to go directly to a list of Inventory records from the Work Order. I used a Reference Rows function like this:
FILTER(โInventoryโ, IN([Product Code], [Related Products][Product Code]))
This gets me the list of Inventory rows that I want to update the Quantity Allocated column for.
I am no longer getting the error message in the log.
Thank you for getting me thinking for an alternative approach!!!
Iโd still like to know the reason for the error. I think logically the structure was ok. Can I not have nested Actions on row sets?
I"m not sure exactly either, but Iโm glad you got it working! (^_^)
FYI, as I test, It appears that the syntax " [Related Products][Product Code]" doesnโt work as I had thought. I thought I would get a list of Product Codes for just the records in โRelated Productsโ. However, it appears I get a list of Product Codes for the entire table Products. Is this expected?
Ok then! Iโll open this as a bug and create a little sample app to simplify the issue I am seeing. For now I have switched to using a SELECT() statement to work around the problem.
Donโt you want [Related Products].[Product Code] ?
The dot is used to dereference a value from a single row. In this case I have a list of rows, like in an actual table.
This syntax ( [Related Products][Product Code] ) is called a โReverse Referenceโ (itโs extremely helpful when youโve got a large amount of data) and basically what it does is the same as a SELECT() to pull a list of values, but only running on the list of related records.
This has the benefit of reducing the dataset needing for the system to run over; imagine if you had 100,000 rows in a sheet, you wouldnโt want to run a SELECT() over that many rows - so you can cut it down by the reverse reference.
But there is a persistent bug where instead of restricting the query to the related rows, they system runs the query over the whole table (giving you everything instead of just the filtered data).
So Related Products is not just a List of References?
Hmm that may have been why I had similar difficulties that I solved in other ways. I mean I know that reverse references existed, I just kept assuming they didnโt do things I wanted to. I thought this was just related to the way IN works. I think the FILTER and IN functions could still work, but I would need to have a much better understanding of the data to make it work. I think I might be doing something similar where a reverse ref would have been nice.
But, hey, as long as you got it working one way or the other.
I must confess, I havenโt kept up with the technical aspects of the processing to truly understand whatโs going on behind the scenes within both AppSheet servers and on the device the app is running.
I hadnโt really thought of [Related Products] as a list of references but I guess that is truly the mechanism being used as opposed as a reference to a list of rows.
Yeah, thatโs why I assume its bugged and not working correctly. In my head, regardless of right or wrong, [Related Products][Product Code] says "give me all the product codes of every related product in the entire Inventory table. Which is why it made sense to me that it wasnโt working since you basically created an IN statement that (basically) always resolves to true. My thinking was the dereference would hopefully force it check for just the list of product codes related to the current entry very much akin to using a [_THISROW] dereference. Maybe I was thinking of something like [_THISROW].[Related Products][Product Code].
I think part of the problem is understanding how some functions works under the hood. For example, we know the FILTER function is just a compact version of SELECT and ultimately that is all AppSheet is doing is translating FILTER to SELECT. And there is an example or two in the docs that tell you that. But we donโt know exactly how AppSheet parses the data to create the actual SELECT it uses. We also donโt have more information on examples on how it does not work. Hence, it ended being easier to write a SELECT yourself that worked.
There are quite a few commands and instances where much, much, much more information would be useful that I ultimately have to come here for.
[Related Things][SomeColumn]
is essentially:
SELECT(
Thing[SomeColumn],
IN([RowKey], [_THISROW].[Related Things])
)
FILTER(table-name, condition)
is equivalent to SELECT(table-name[key-column], condition, false)
. Itโs essntially a macro. Thereโs no magic in it.
Similarly, LOOKUP(search-expression, table-name, search-column-name, result-column-name)
is essentally a macro for ANY(SELECT(table-name[result-column-name], (search-expression = [search-column-name]), false))
.
Can you elaborate?
There were some examples somewhere of common problems people have with SELECT and how it doesnโt work.
I actually decided to start writing up a seperate post altogether describing the problem Iโm having. Iโll @ you in the post.
Wouldnโt โ[Related Products][Product Code]โ be the same as โ[_THISROW].[Related Products][Product Code]โ in the context in which it was used above? It is my understanding that you really only need โ[_THISROW]โ to resolve ambiguity.
With regards to knowing how things work under the hood, I would agree. And it would be great if there was a more comprehensive knowledge base we could go to get answers.
On the other hand, creating such a knowledge base is time consuming which translates to expensive. And it is only as good as the last update! So its kinda a catch-22.
On the bright side, its always good to chat with like minded people to figure our โproblemsโ out together!!
I guess it depends. I donโt know if any of my suggestions would end up computing correctly. I was thinking of it, because I saw this in the expression builder:
Ref lists are weird beasts. I am having my own hard time with a ref enumlist. The data all works perfectly. Itโs the dashboard interactive part it doesnโt like.
EnumList of type Ref is really not well supported. What are you trying to do?
Inside SELECT()(-ish) functions, that have their own frames of reference, [_THISROW]
gives you direct access to the row from which the SELECT() was invoked. I recommend always using [_THISROW]
if you intend to reference the enclosing row, else you run the risk that an identically-named column will be added to a scanned table and produce ambiguity and confusion unexpectedly.
Outside a SELECT()(-ish) function, [_THISROW]
is equivalent to dereferencing the current rowโs key column. So if your key column is RowKey, [_THISROW]
and [RowKey]
are equivalent.
SELECT()(-ish) functions are: FILTER(), LOOKUP(), MAXROW(), MINROW(), and SELECT().
Understood and a very good practice (which I utilize myself). BUTโฆI donโt believe its required. I remember when I first started creating complex conditions. I found all the _THISROW references were making it hard to read the condition. I started naming columns uniquely to avoid this.
For example, in Product table I would have had โProduct Codeโ while in Purchase Order table I might have had โPO Prod Codeโ. This is considered by some a good db naming practice as you can easily identify the source table from the name - especially in error msgs.
Anyway, my point is that you will not need _THISROW if columns are named in this manner. AppSheet can resolve without conflict.
And by the way, I have relaxed on such naming conventions. Getting lazy I guess!
User | Count |
---|---|
43 | |
28 | |
23 | |
14 | |
14 |