API Calls - These Seem to Be Appsheet's Rubix Cube

JohnnyG · March 5, 2024, 9:46pm

Hello, There are a lot of things that it seems that we can do with API Calls, but once i get past the “add” action, and start trying to get into nested start expressions, i get super confused. I would love to learn more, and talk about this with folks that get it.

MultiTech_Visions · March 6, 2024, 6:53pm

Yeah it can be confusing to keep track of things when you start nesting things.

The real trick is to make sure you keep the [_thisrow-n] correct, which is the tricky part.

Tip: think about your API nesting in the same way that you can for PDFs

The way I keep track of what number corresponds to what layer is by creating a temporary list to help me keep track of the layers.

Let’s say I’m printing a 3 layer table:
1. Orders
2. Order_Details
3. OD_Fulfillments

Let’s say for instance that we want to ADD a Fulfillment record for each Details:

The root table context would be Orders, as that’s the table from which we’ll kick off the automation.
- Our target table is the OD_Fulfillment table.
From the Orders table, we’ll then begin looping through the Order_Details, with the intent to create an OD_Fulfilliment record for each.

Your JSON body might look something like this:

<<START: [Related Order_Details]>>
{
  "OD_Order_Detail_Link": "<<[Order_Detail_ID]>>"
}
<<END>>

Since we’re in the table context of Order_Details, we don’t need to specify any [_thisrow-n] and can just use a regular column name template variable.

Let’s now say that I want to EDIT these OD_Fulfilliment records

Let’s say that I have a value from the Orders table that I want to copy into each of the records.

Our root table context is Orders as this is where we’re launching the automation from.
From the Orders table, we’ll begin looping through the OD_Fulfillments table making an edit in each.

Your JSON body might look something like this:

<<START: Split(Concatenate([Related Order_Details][Related OD_Fulfillments]), " , ")>>
{
  "OD_Fulfillment_ID": "<<[OD_Fulfillment_ID]>>", 
  "OD_Update_Column": "<<[_thisrow-1].[Order_Col_With_Update]>>"
}
<<END>>

Since OD_Fulfillments is not ref connected to Orders, there is no [Related OD_Fulfillments] on the Orders table.
There is one on the Order_Detail table however, and we can use a list dereference to bring those values up one layer.
- This requires that we reformat the data so it’s in the appropriate format for us to use - hence the Split(Concatenate(... stuff.

Let’s Get Nutz

Let’s say that I’m creating records in a 4th layer table, and for demonstration let’s pull a value from each of the previous layers!

Our root table context is Orders
- We then will being looping through the Order_Details
- Then we will loop through the OD_Fulfillments

<<START: [Related Order_Details]>>
<<START: [Related OD_Fulfillments]>>
{
  "4th_Layer_Value_From_ORDER": "<<[_thisrow-2].[Order_Value]>>", 
  "4th_Layer_Value_From_ORDER_DETAILS": "<<[_thisrow-1].[Order_Detail_Value]>>", 
  "4th_Layer_Value_From_OD_FULFILLMENT": "<<[OD_Fulfillment_Value]>>"
}
<<END>>
<<END>>

You can really start to get a sense of how the numbers work with this example.
- See how the [_thisrow-n] works backwards through the layers?

What sort of problems are you struggling with conceptualizing @JohnnyG?

JohnnyG · March 21, 2024, 6:36pm

@MultiTech_Visions , thanks again for spending time with this today during our meeting. I think i have a better understanding of this now. The issue i was having was the perspective i was looking at things. In my head, because i have been “trained” to see in references between tables, i was thinking of “layers” in the parent child relationships in my tables and having a hard time bringing in information from another table that was related, but wasn’t in the “layers” of the parent and child. I was NOT thinking in the context of layers within the start statements… Once that clicked in my head, i think i am headed in the right track.

MultiTech_Visions · March 21, 2024, 9:47pm

Your realization of this might help others grasp the complexities of working with nested start statements.

SkrOYC · March 25, 2024, 5:11pm

Context has always been one of the most important aspects of AppSheet expressions.
Let me add some comments that may help as a general rule to follow when using [_THISROW-n].
I’m going to use Start: expression usage as an example since may be easier to get the idea around a change of context.

Current row is always under [_THISROW], the reason for that is that the expressions are almost 100% of the time evaluated in the context of a row or record of data.
When using a Data change as the bot event, that row is going to be [_THISROW] inside any expression no matter how deep you are in a Start: or nested Start: context.
When using an Schedule bot that is evaluated “ForEachRowInTable”, then it’s the same as a Data change without a change, you will be in the context of a row of the selected table.
When using an Schedule bot that is not evaluated “ForEachRowInTable”, you will break the matrix -be cautious.

Current row from an automation POV is always [_THISROW], which is a reference to the key column

Then, if you start using Start: expressions, you will basically go deeper and deeper as long as your relationships can let.

Assuming this is in the context of a row of Table A

{
  'Key': '<<[_THISROW]>>', // This is the key value for this row in TableA
  'List': {
    <<Start:[Related TableBs]>> // Assuming there is a relationship
    'Key': '[IdColumn]',
    'TableA Key': '[_THISROW-1]', // One level above this
    'Origin Table Key': '[_THISROW]', // This also gives you the key value on Table A,
    'List': {
      <<Start:[Related TableCs]>> // Assuming there is a relationship
      'Key': '[IdColumn]',
      'TableB Key': '[_THISROW-1]', // One level above this,
      'Origin Table Key': '[_THISROW]', // This gives you the key value on Table A,
      'TableA Key': '[_THISROW-2]', // Two levels above is Table A as well
      <<End>>
    }
    <<End>>
  }
}

A good reference can be found in Steve’s explanation as well, in his case using SELECT()

Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions):
SELECT(
 table[column1],
 ...
   SELECT(
     table[column2],
     ...
     SELECT(
       table[column3],
       ...
     )
   ...
   )
 ...
)
Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row.

Within this entire expression, we can refer to column values of the Origin Row by dereferencing [_THISROW]. For instance, to get the value of the Customer Name column of the Origin Row, we can use [_THISROW].[Customer Name].

Let’s also name each of the nested queries within the entire expression:

Query 1: SELECT(table[column1], ...) (outer-most query)

Query 2: SELECT(table[column2], ...)

Query 3: SELECT(table[column3], ...) (inner-most query)

Query 2 is nested inside Query 1. Within Query 2, column values of the row currently being examined by Query 1 can be accessed by dereferencing [_THISROW-1]. For instance, to get the value of the column1 column of the Query 1 row, we can use [_THISROW-1].[column1]. The -1 in _THISROW-1 refers to the query one step outside the current query.

Query 3 is nested inside Query 2. [_THISROW-1] refers to the query one step outside the current query, so within Query 3, [_THISROW-1] refers to the current row of Query 2. Query 2 is itself nested within Query 1, so Query 1 is two steps outside Query 3. [_THISROW-2] can be dereferenced to access the values of columns in the query two steps otside the current one: Query 1.