Error when trying to process rows in a Related fie...

WillowMobileSys · 09-04-2019 06:15 AM

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”
}

MultiTech

What type of workflow is this? A data change?

The error is saying that something is blank, or more likely perhaps just not set yet.

WillowMobileSys

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?

MultiTech

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??

WillowMobileSys

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.

WillowMobileSys

@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?

MultiTech

I"m not sure exactly either, but I’m glad you got it working! (^_^)

WillowMobileSys

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?

MultiTech

Negative, this is a bug.

@Aleksi @praveen

WillowMobileSys

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.

Bahbus

Don’t you want [Related Products].[Product Code] ?

WillowMobileSys

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.

MultiTech

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).

Bahbus

So Related Products is not just a List of References?

Bahbus

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.

WillowMobileSys

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.

Bahbus

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.

Steve

[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?

Bahbus

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.

WillowMobileSys

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!!

Bahbus

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.

Steve

EnumList of type Ref is really not well supported. What are you trying to do?

Steve

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().

WillowMobileSys

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!

Error when trying to process rows in a Related field list