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.
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.
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.
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.
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.
๏ผฉ 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?
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?
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.
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",
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.
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:
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?
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.
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โ
}
The Locale value in Properties looks strange. It should be โen-USโ.
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โ
}
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.
Thanks, Phil. I checked the option.
Itโs Mar2020Test-1064078
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โ
}
]
}
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โ
}
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
Are you sure [Item] field on the Pack Details table is a really KEY of that table?
Yes, itโs both key and label
Sounds like workflow for api is fired. Why the full error message you see in audit log after failing api?
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โ
}
Hi Chris,
Iโm currently feeling your pain. You didnโt happen to come upon a solution to this issue, did you??
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |