Webhook/API to order individual items

[SpaceCoyote]

My app for placing orders has different packs available, each containing different items.

For example, the PackDetails table:

PackName | Item | Qty
Fruit pack 1 | Apples | 1
Fruit pack 1 | Oranges | 2
Fruit pack 1 | Pears | 1
Fruit pack 2 | Plums | 2
Fruit pack 2 | Melons | 3

Orders can only be placed for complete packs, and up until now I’ve been breaking out the individual items on the back-end with Excel macros. I was reading a bit about using a webhook/API to add the different line items on the order automatically. So, if a customer orders
2 of fruit pack 2 (held in the Orders table), it would add 4 plums and 6 melons (by referencing the PackDetails table) to the OrderLines table for processing.

I followed the example that uses workflow to clone a row to another table after adding an item, and I at least got that working - finally. The select statements are proving much harder for me, so if someone has time to help me out I’d appreciate it.

[Koichi_Tsuji]

I do have apps where the user place the number of set (pack in your case), for instance, they order 10 complete set on PO. Then this stands as parent table, and then I run workflow, webhook to add child rows to add all the elements. For instance, one complete set is coming from 10 parts. When the new PO is in place, then user will see 10 lines of items as child.

This is using little complicated trick, using webhook, adding new rows.

It is depends on how you would like to set your app in complex way, but if your OP only allow a certain number of pack, then you might be able to set you app easily.
Let s say, one PO accept max 3 distinct sets.

you create five physical column.

Select pack #1
Select pack #2
Select pack #3

Then create three virtual column

select(PackDetails[PackDetailTableIDColumn],[PackName]=[_thisrow].[Select pack #1])
select(PackDetails[PackDetailTableIDColumn],[PackName]=[_thisrow].[Select pack #2])
select(PackDetails[PackDetailTableIDColumn],[PackName]=[_thisrow].[Select pack #3])

This will dynamically display the packdetails as list associated with the selected pack names.

Again, this is just displaying, not adding any new rows to any other table.

If your requirement reside to add something to another table, webhook is the best option.

[SpaceCoyote]

Thanks for the reply. I would be looking to add the contents of the pack to another table. Items quantities within the pack may be different, as in my plums and melons example. I’m just having a hard time figuring out the code for the webhook.

[Koichi_Tsuji]

Sounds like you need to dynamically add rows to other tables, which Appsheet API can manage.
Setting up API is one of the toughest part to deal with in Appsheet, but at least it will increase the usability of Appsheet.

I m sure you are looking into docs incl https://help.appsheet.com/en/articles/1980012-adding-records-to-a-table

I always refer to those docs when it comes to Appsheet APIs.

[SpaceCoyote]

Yeah, I read through that and some others, including the one about <<Start:SELECT statements -
https://help.appsheet.com/en/articles/961746-template-start-expressions.

I’m having trouble figuring out the syntax, though. I’d need to get the Item and Qty from the PackDetails table for each line item within a pack.

[Koichi_Tsuji]

Ｉ understand when you run API on the app, you see error, or API is not doing your job. I face those situation all the time, but always visit Audit log, to find the root cause of error. Did you check that?

[SpaceCoyote]

I did work through the audit log in order to get the sample on copying an order to a table, but the actual coding with <<Start: Select is what’s stalling me.

Maybe there is a way to accomplish it within appsheet though, without using the API. Based on your earlier suggestion I created two virtual columns: one for items and one for qty:

Then, using a data action to add new row to table with INDEX(SPLIT([Items],","),1), I was able to write Box1 to the new table. This could be duplicated for qty to give me what I need. However, since the length of the list varies between different packs (and could be 30 or more items), I need to find a way to add them dynamically. If the index could be a count of the items, something like a length or count function, then maybe it could work that way?

[Koichi_Tsuji]

Possibly other may have better idea rather than me, but in your case, I can not come up with a way to dynamically count the length of the list and repeat the actions as many as the number of count of the list.
As I stated before, if you have option to use API, I still believe API is one you should take.

[SpaceCoyote]

Thanks for trying, I appreciate it. I need someone that knows a lot about the API coding part. For example, this webhook JSON file works fine and inserts Promotion Name into the new table:

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,
“UserSettings”: {
“Option 1”: “value1”,
“Option 2”: “value2”
}
},
“Rows”: [
{
“Promotion Name”: “<<[Promotion Name]>>”
}
]
}

When I try to change it to a select statement to gather the items, it fails:

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,
“UserSettings”: {
“Option 1”: “value1”,
“Option 2”: “value2”
}
},
“Rows”: [

"<<Start:",
"SELECT(Pack Details",
[ "Item" ],
",",
[ "Promotion Name" ],
"=",
[ "_THISROW" ],
".",
[ "Promotion Name" ],
")>>",
"<<End>>"

]
}

The audit log gives me the error below. I’ve been trying all morning to tweak the code and try all kinds of different ways, but have made no progress. If someone could show me exactly where it’s going wrong, I can probably figure the rest of it out.

“Errors”: “Error: Workflow rule ‘Add Order Lines’ action ‘Action’ Body template. Expression '”,“SELECT(Pack Details”,[ “Item” ],",",[ “Promotion Name” ],"=",[ “_THISROW” ],".",[ “Promotion Name” ],")’ is invalid due to: Expression refers to undefined field. Error: Workflow rule ‘Add Order Lines’ action ‘Action’ Body template. Start expression ‘",“SELECT(Pack Details”,[ “Item” ],",",[ “Promotion Name” ],"=",[ “_THISROW” ],".",[ “Promotion Name” ],")’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table. Error: The webhook body is empty",

[Koichi_Tsuji]

You need to rewrite your api body for sure.

Forget about API body in JSON format for now and leave it aside, but could you type your required expression for Select(xx, xxx) ?

I know you want to select “Pack Details” table, but not sure rest of select expression you wish to build.

[SpaceCoyote]

Short version: SELECT(Pack Details[Item], [Promotion Name] = [_ThisRow].[Promotion Name])

Longer version:

Breaking the whole process down, which I probably should have done from the start:

1. The customer adds the promotion (pack) and desired qty to the [Order Details] table via the app form.
2. There is a workflow action on this table (ADDS_ONLY) that runs the API and adds rows to my new [Order Lines] table.

If I run my first set of code above, it will successfully grab the Promotion Name, Qty or whatever I tell it, from the [Order Details] table and add it to [Order Lines].

Instead, what I want it to do is take that Promotion Name and grab all the items and quantities within that promotion (pack) from [Pack Details] and add the individual components to [Order Lines].

I’ll worry about the qty and calculations for that later, but for just the items it should be something like what I tried:

SELECT(Pack Details[Item], [Promotion Name] = [_ThisRow].[Promotion Name])

Indeed, if I run this code as a virtual column on [Order Details], it produces a list Item1, Item2, Item3 etc. Maybe I’m missing a step?

[Koichi_Tsuji]

Thanks, mate.
One thing at a time, that is best practice for coding, as well as developing app on Appsheet.

I m not perfectly sure the JSON body, but If I rewrite the JSON body for you, it should looks like -

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,

},
“Rows”: [

<<Start: Select(Pack Details[Item],[Promotion Name] = [_ThisRow].[Promotion Name])>>

{
“Promotion Name”: “<<[Promotion Name]>>”,

  "Qty": "<<[_thisrow].[PRODUCT NO]>>" 

},
<<End>>

]
}

This JSON should work when you set the target table of [Order Lines] s ID / key colum to be auto genenerated, i.e. with expression of UniqueID() or other as initial value.]

I m sure you will fail to run API, but curious to see how Appsheet Audit log would say why error is coming up.

[SpaceCoyote]

Thanks! I think it’s close.

I tweaked the code a bit and it’s finding the items and quantities from Pack Details

  "Rows": [
    "<<Start: Select(Pack Details[Item],[Promotion Name] = [_ThisRow].[Promotion Name])>>",
    {
      "Promotion Name": "<<Item>>",
      "Quantity": "<<[_thisrow].[Quantity]>>"
    },
    "<<End>>"
  ]

It still errors out, but…almost

Audit Log Details
Error:
“Webhook HTTP post request failed with exception {“Message”:“REST API invoke request failed: Failed to get API ‘Rows’ due to: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’…”} The remote server returned an error: (400) Bad Request.”
Properties:
{
“AppId”: “47a6d648-7390-4dbc-ba4e-c5991194f45b”,
“AppTemplateVersion”: “1.000091”,
“RuleName”: “Add Order Lines”,
“EventType”: “Change”,
“InvokedBy”: “Update”,
“ServerName”: “produ21ea00000H”,
“ServerRegion”: “EAST-US”,
“IgnoreSecurityFilters”: false,
“TableName”: “Order Details”,
“RuleTableName”: “Order Details”,
“OperationUpdateMode”: “ADDS_ONLY”,
“EventMatch”: “Workflow event successfully matched”,
“Condition”: “”,
“MatchesCondition”: “True”,
“ActionResults”: “Created 1 ActionResults”,
“Action Type”: “Webhook”,
“Action Name”: “Action”,
“Url”: “https://api.appsheet.com/api/v2/apps/47a6d648-7390-4dbc-ba4e-c5991194f45b/tables/OrderLines/Action”,
“Verb”: “Post”,
“MimeType”: “application/json”,
“Headers”: “applicationAccessKey:NprkC-aNWi2-45bnH-nC1pS-w2K9H-zYN36-D2VMN-8alpR, _webhookActionDepth:0”,
“Payload”: “{“Action”: “Add”,“Properties”: {“Locale”: “\“en-US�,”,“Location”: “47.623098, -122.330184”,“Timezone”: “Pacific Standard Time”},“Rows”: [””,{“Promotion Name”: “Sock1”,“Quantity”: “1”},",",{“Promotion Name”: “Sock2”,“Quantity”: “1”},""]}",
“AppTemplateName”: “Mar2020Test-1064078”,
“Operation”: “Workflow action”,
“Result”: “Failure”
}

[Phil]

The Locale value in Properties looks strange. It should be “en-US”.

[SpaceCoyote]

You’re right. When I copied the code, it added extra quotes around almost everything and I had to go through line-by-line and edit them. I must have missed that one, thanks Phil.

It still throws the cast error, though:

Error:
“Failed to get API ‘Rows’ due to: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’…”
Properties:
{
“RestAPIVersion”: 2,
“TableName”: “OrderLines”,
“AppTemplateVersion”: “1.000091”,
“AppTemplateName”: “47a6d648-7390-4dbc-ba4e-c5991194f45b”,
“Operation”: “REST API invoke”,
“RecordType”: “Stop”,
“ResultSuccess”: false,
“StatusCode”: “BadRequest”,
“Result”: “Failure”
}

[Phil]

I may be able to investigate if you go to https://www.appsheet.com/account/account#_tab_acctConfig and check the option that enables support access.

I have some critical work to finish over the next few days, so I am not sure how quickly I can investigate your issue.

[SpaceCoyote]

Thanks, Phil. I checked the option.

It’s Mar2020Test-1064078

[Phil]

There appear to be bogus quotes and commas in the midst of the Rows values.
It may be part of the “When I copied the code, it added extra quotes around almost everything” problem.

In the Audit History I see:

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
“”,
{
“Promotion Name”: “Sock1”,
“Quantity”: “1”
},
“,”,
{
“Promotion Name”: “Sock2”,
“Quantity”: “1”
},
“”
]
}

Rather than:

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
{
“Promotion Name”: “Sock1”,
“Quantity”: “1”
},
{
“Promotion Name”: “Sock2”,
“Quantity”: “1”
}
]
}

[SpaceCoyote]

Hmm, I don’t see anything in the code that would account for that. I even went back and typed it all out from scratch.

{
  "Action": "Add",
  "Properties": {
    "Locale": "en-US",
    "Location": "47.623098, -122.330184",
    "Timezone": "Pacific Standard Time"
  },
  "Rows": [
    "<<Start: Select(Pack Details[Item],[Promotion Name] = [_ThisRow].[Promotion Name])>>",
    {
      "Promotion Name": "<<Item>>",
      "Quantity": "<<[_ThisRow].[Quantity]>>"
    },
    "<<End>>"
  ]
}

I did see an additional error on the API invoke:

Character 106 is the P in Pacific, so maybe that’s what hanging it up?

Error:
“Webhook HTTP post request failed with exception {“Message”:“REST API invoke request failed: Failed to get API ‘Rows’ due to: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’…”} The remote server returned an error: (400) Bad Request.”
Properties:
{
“AppId”: “47a6d648-7390-4dbc-ba4e-c5991194f45b”,
“AppTemplateVersion”: “1.000091”,
“RuleName”: “Add Order Lines”,
“EventType”: “Change”,
“InvokedBy”: “Update”,
“ServerName”: “produ21ea000000”,
“ServerRegion”: “EAST-US”,
“IgnoreSecurityFilters”: false,
“TableName”: “Order Details”,
“RuleTableName”: “Order Details”,
“OperationUpdateMode”: “ADDS_ONLY”,
“EventMatch”: “Workflow event successfully matched”,
“Condition”: “”,
“MatchesCondition”: “True”,
“ActionResults”: “Created 1 ActionResults”,
“Action Type”: “Webhook”,
“Action Name”: “Action”,
“Url”: “https://api.appsheet.com/api/v2/apps/47a6d648-7390-4dbc-ba4e-c5991194f45b/tables/OrderLines/Action”,
“Verb”: “Post”,
“MimeType”: “application/json”,
“Headers”: “applicationAccessKey:NprkC-aNWi2-45bnH-nC1pS-w2K9H-zYN36-D2VMN-8alpR, _webhookActionDepth:0”,
“Payload”: “{“Action”: “Add”,“Properties”: {“Locale”: “en-US”,“Location”: “47.623098, -122.330184”,“Timezone”: “Pacific Standard Time”},“Rows”: [”",{“Promotion Name”: “Sock3”,“Quantity”: “1”},",",{“Promotion Name”: “Sock4”,“Quantity”: “1”},""]}",
“AppTemplateName”: “Mar2020Test-1064078”,
“Operation”: “Workflow action”,
“Result”: “Failure”
}

[SpaceCoyote]

Ok, so the problem is definitely with the SELECT statement. I tried pulling in the Quantity field from the Pack Details table and I get Error 1 below. This is apparently because Quantity isn’t the Key column.

Code 1 :
“<<Start: SELECT(Pack Details[Quantity], [Promotion Name] = [_ThisRow].[Promotion Name])>>”
Error 1:
Start expression ‘SELECT(Pack Details[Quantity], [Promotion Name] = [_ThisRow].[Promotion Name])’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.

When I run the original code line, it would seem that it is generating the list, but is running into some casting error. This has been a long, on-going process, and I’d like to have something to show for it if someone can help further.

Code 2:
“<<Start: SELECT(Pack Details[Item], [Promotion Name] = [_ThisRow].[Promotion Name])>>”
Error 2:
Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type 'Newtonsoft.Json.Linq.JObject

[Koichi_Tsuji]

Are you sure [Item] field on the Pack Details table is a really KEY of that table?

[SpaceCoyote]

Yes, it’s both key and label

[Koichi_Tsuji]

Sounds like workflow for api is fired. Why the full error message you see in audit log after failing api?

[SpaceCoyote]

It’s the same as posted above:

Error:
“Failed to get API ‘Rows’ due to: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’…”
Properties:
{
“RestAPIVersion”: 2,
“TableName”: “OrderLines”,
“AppTemplateVersion”: “1.000099”,
“AppTemplateName”: “47a6d648-7390-4dbc-ba4e-c5991194f45b”,
“Operation”: “REST API invoke”,
“RecordType”: “Stop”,
“ResultSuccess”: false,
“StatusCode”: “BadRequest”,
“Result”: “Failure”
}

[aforrester]

Hi Chris,

I’m currently feeling your pain. You didn’t happen to come upon a solution to this issue, did you??