Work Flow Start Error

[Dan_Lawrie]

Hi,

Hopefully someone can help, I am trying to create a workflow email rule, but I keep getting an error message that I can not seem to figure out what its telling me, and no examples for what I am looking for.

My email should consist of the order details from a new order, so each order can have multiple rows all with their own id.

The table I have set up in my template has these details;

Column “Product Code”
<<Start:[Order Details]>><<[Product ID].[Product Code]>>
At this point I want that column to lookup all the rows with the matching order id and return the product ID

Column “Description”
As above but return the description

Column “Quantity”
As above but return the quantity

Column “Line Value”
As above but return the Value

So if one order has two lines (OrderDeatails ID) its should return two lines, if it has four its should return and so on.

Thanks in advance

[LeventK]

@Dan_Lawrie
Which table does your [Product ID] refers to? (as you have used a de-ref expression)

[Dan_Lawrie]

Product ID refers to the table Order Details

[LeventK]

@Dan_Lawrie
So you should have a column i.e. [Related Order Details] virtual column with the REF_ROWS(…) expression.

[Dan_Lawrie]

@LeventK I have just figured that out two minutes ago, but it has now presented another problem.

Product ID is a ref to the table Products, however I need this to automatically update using a Lookup of similar formula, as the customer will be using this app, I dont want them looking at random digits trying to figure out what they need, however the lookup formula will not work, it selects the first item in the table and sticks.

Without the [_THISROW] before hand, the formula does not work, and with it, i get a circular formula error,
My formula is this
IF(ISBLANK([_THISROW].[Product])=TRUE," ",LOOKUP([_THISROW].[Product], “Products”, “Product”, “ID”))

Do you have any ideas on how I can get round this?

[LeventK]

@Dan_Lawrie
You don’t need the = TRUE statement at all

IF(ISBLANK([Product])," ",LOOKUP([_THISROW].[Product], “Products”, “Product”, “ID”))

[Dan_Lawrie]

@LeventK thank you, thats resolved.

[LeventK]

You’re welcome

[Dan_Lawrie]

@LeventK I do have one more issue that has started over the last hour and no matter what I try I can not resolve it, for some reason now, my work flow emails are coming through with blank order details, yet I have no errors?

Is this something you have experienced.

[Lynn]

Hi @Dan_Lawrie Do you have anything in “IF this is True” ?

[Dan_Lawrie]

@Lynn I do not

[Dan_Lawrie]

Hi @Lynn

I have tried deleting all IF(ISBLANK formulas and stick with a simple lookup, however that does not seem to resolve my issue either.

[LeventK]

@Dan_Lawrie
Possibly the your start/end expression is configured incorrectly provided you are sure that there are related records to be returned.

[Dan_Lawrie]

@LeventK but if my start/end expressions were configured incorrectly surely I would get an error message?
There are definitely related records to be returned and I have a lookup formula to ensure that, which is needed, could this be the reason why?

[LeventK]

@Dan_Lawrie
With the word “incorrect” I had referred to the expression itself rather than its syntax. You will surely and possibly get an error in the audit log if the syntax is incorrect but if the expression is not correct than it will possibly return null value.

[Dan_Lawrie]

Hi @LeventK this is the table I have built, The first column should look into the OrderDetails virtual column, then column Product ID within that virtual lookup is referenced to the table Products.
At this point it should return the value of Part No. within the table of Products that where the two key column ID matches with the ref column Product ID

If that makes sense, or have I overcomplicated this.

[LeventK]

@Dan_Lawrie
I understand that:
1. You have renamed your REF_ROWS virtual column from [Related Order Details] or similar to [Order Details]
2. You have 2 child records for this particular Order ID
Could you pls verify?

[Dan_Lawrie]

@LeventK

Yes you are correct on both accounts.

[Dan_Lawrie]

@LeventK I have been into the test function of the wrokflow rule and everything appeared to be fine, so from there I sent the email again and all products were loaded onto the email OK.

It only seems to send blank when the order is brand new, as though the rule has not waited for the sync to complete before sending the email.

Can I create a 2 minute delay before sending the email?

[Aleksi]

Please check this sample how you can send the email with child records… https://www.appsheet.com/samples/Send-an-email-when-children-are-added?appGuidString=75e6fdb0-7d89-4...

[Dan_Lawrie]

@Ales_Marek I have followed this to the letter, but now I do not get any emails at all, not even a blank one as I did before.

[Aleksi]

There is another sample app if it would help… check the app called “Parent Workflow” from https://www.appsheet.com/portfolio/531778

[Dan_Lawrie]

@Aleksi as much as I appreciate your answers, they are not helping, I cant see what triggers ther emails to be sent from these examples.

I have followed them and they do not update the record to true, so therefore do nor trigger the email.

[Dan_Lawrie]

@Aleksi what the examples do not explain is what triggers this action, surely something has to say this has happened so I need to change the value of that row to true?

[Aleksi]

I believe the quickest way is if I check your app, is that okay?

[Dan_Lawrie]

@Aleksi yes that would be fine how would it be able to show you without making it public?

[Phil]

Hi Dan,

The first thing I would do is to fix the keys for the Order Details, Customers, and Products tables.

The key for the Order Details table should be “Order Detail Id”. It is current a compound key containing “Order Detail Id” and “Product Id”. Since “Order Detail Id” is a UNIQUEID field, adding “ProductID” as part of the key just confuses things and makes your life harder.

The key for “Customers” is row number. You should never use row number as a key. If “Name” is unique and invariant (its value never changes) you could use “Name” as the key. Otherwise, add a new “Customer Id” text field with an initial value of UNIQUEID(). If I were doing it, I would do the later.

The key for the Products table is currently a UNIQUEID field called “ID”. That is fine, but it would be more consistent to call it “Product Id”. It is a small point, but consistency is your friend.