Recalculating app formulas in second unreferenced table

[jaap_knoxx]

I am stuck on a problem after migrating from spreadsheet to app formulas.

We have company-wide settings used in app formulas in a second table. We would like to re-calculate these app formulas when any of these settings are changed. Also, it would be nice to be able to manually “force” a re-sync if things go haywire or we change the formulas.

Table 1: Company Settings
Item | Value
1AUD= Pound | 0.55
1AUD= USD | 0.70
Shipping cost UK per kg | $5.00
Shipping cost US per kg | $3.00
Minimum shipping cost | $15.00
Minimum profit margin | 15%

Table 2: Items
Name | Description | Country | Weight | Currency Exchange Rate
Item 1 | … | England | 5kg | <app formula>
Item 2 | … | US | 7kg | <app formula>

This is an example app formula used for a field in the Items table:
LOOKUP(
IF([Country]=“England”, “1AUD= Pound”, “1AUD= USD”),
“Company Settings”, “Item”, “Value”)

I have tried the following:

1. An Action with “Data: execute on a set of rows” with Referenced Rows column “SELECT(Items[Item Id], 1=1)” with the Action: Edit. However, I suspect this is not working because the table is not referenced so no rows are selected.

2. A workflow with a custom action and the following configuration:

URL: https://api.appsheet.com/api/v2/apps/.../tables/Items/Action
Body:
{
“Action”: “Edit”,
“Rows”: <<Start: SELECT(Items[Item Id], 1=1)>><>
}

This gives the following error:
“ErrorCode”: “ERROR_WORKFLOW_WEBHOOK_BODY_INVALID”,
“Description”: “Failed to parse JSON due to Unexpected character encountered while parsing value: }. Path ‘Rows’, line 1, position 25… Invalid JSON value starts with: }”

Thanks in advance!

[TyAlevizos]

Hi, a different way to solve this - may work in your case or may be unfeasible due to your data model and data model requirements - is to use a Lookup/REF pattern here. You have items with a local currency, and you want to look up an AUD conversion rate. The following app simplistically shows how to do this. In this approach, you don’t need actions, refreshes, workflows. It’s just “built in”. Is this helpful or the kind of thing you were looking for?

https://www.appsheet.com/samples/Built-from-your-app-spec-?appGuidString=5978bbee-f986-485e-aac5-a28...

[jaap_knoxx]

Each lookup item is something completely different. Some are just global settings such as minimum profit margins used in calculations.

Just double checked Ref to see if we can modify the lookup value using an expression which could have worked. Eg. converting “England” to “1Aud= Pound” and then in another field converting “England” to “Shipping Costs - UK” but I don’t think that’s possible.

Any other suggestions?