Create an action that checks if LAST row of CHild's table has a specific value BLANK

AppSheet AppSheet Q&A
,
1

Hi,

I’m building an action that creates a new row in the child’s table from the parent deck view using this:

LINKTOFORM(Route_Form,RDV ID, [RDV ID])

Simple enough, but I want it to be a bit smart,

That is, IF the LAST ROW of the table has one Specific value BLANK, I want instead of creating a new Row, to EDIT the last row.

I’m diving deep into “IF” “Select” and “LINKTOROW” but to be honest, I’m a bit lost,

I know from an IF Expression I will have to use a “SELECT”,“LOOKUP”,“MAX” or one of those expressions to check if the Last collumn of the Child’s table has value, then when I get that I need to wrap that expression in a “ISBLANK” Expression so that it checks it. Then I need to Create a “LINKTOROW” expression that gives the specific row to the formula, so that I get to the form view of the row I want,

Finaly, ELSE all of that, I want the code I added earlier, to create a new row.

Things is, I’m.. pretty sure ? I get the idea of it, I read this : https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-FILTER-LOOKUP-MAXROW-MINROW-REF-ROWS-and-SELECT/m-p/366399

And got a bit better in understanding the Expressions, but I can’t find how to check a row in a CHILDS table from there.

Any guidance would be appreciated

thanks

2

Perhaps something like this:

IF(
  ISBLANK( LOOKUP( MAXROW( 
    table , _RowNumber/creation_timestamp ) ,
    table , key-column, column-to-check ) ) ,
  LINKTOROW( same-MAXROW-expression-as-above , "form_view" ) ,
  LINKTOFORM(...)
)
3 Likes
3

I get this Error:

Function ‘LOOKUP’ must be passed constant values for its tableName, lookupColumn, and returnColumn parameters: LOOKUP(lookupValue, tableName, lookupColumn, returnColumn). Consider wrapping the constant parameters in double quotes to ensure correct parsing.

Also, it seems the link to row expression is looking for the “Child ID” key not the “Parent Id” Key

Forget this, I made the expression work in the editor, However I do not get the expected result. It Creates a new row everytime, I guess i’m pointing in the wrong direction

4

May I ask what the

_RowNumber/Creation_timestamp

Means to you ? Would I need to implement a Timestamp collumn for the “Child” TAble?

I already have an “Order” collumn that goes incrementaly

5

If you’re executing this action from the perspective of the parent record…

Two part solution

  1. Create a VC to hold the last child record (efficiently) on the parent level

    Index([Related Whatever], count([Related Whatever])

    - The system will default this to a hard ref when you make the column
    - Change it to an enum ref

  2. Now, in your action, you have access to the values from the last child record

if(IsBlank([Parent].[Last_Child_Record].[Column]), 
	LinkToForm("FORM_NAME_HERE"
		"CHILD_TABLE_REF_COLUMN", [Parent]
	),
	
LinkToRow([Parent].[Last_Child_Record], "VIEW_NAME_HERE")
)

Benefit: None of this re-queries your database at all

  • It simply pulls data that’s already there
3 Likes
6

Thanks, seems very wise,

probably stupid question but… What’S an Enum Ref ? I can’t seem to find it in the VC dropdown. I’ve tried setting it to a ENUM or ENUM LIST but I get this error:

Column PArent_LastRoute in expression '[Parent ID].[Parent_LastRoute].[ChilCollumnToCheck]' does not contain a reference.

Scratch that, I’m dumb found it :

Capture d’écran, le 2022-08-04 à 10.10.23.png
Capture d’écran, le 2022-08-04 à 10.10.23.png1164×860 48.2 KB

7

Wow, there’s… a surprising lack of documentation about this
- :thinking: hmmm… time to add that to the video list!

Enum is the root column type

  • It’s base type should be set to “Ref”
    • Which will then ask you what table that’s a reference too

This setup functions similarly to a hard-ref (a Ref type column); you don’t get the reverse reference, but you can still de-reference data out of the list

I’ve got a sample app that show various things about this setup:

3 Likes
8

OKay, so it WORKS~~, ISHHHH~~

Here’s the thing : If I create a New child Row, From the Parent, then save the CHILD AND THE PARENT,

It It brings me to the Right last Row IF the selected collumn is, in fact, empty

BUT

If It Use the button then, it saves the row, but on the CHILD side, and since the “LastChildRow” lives in the PARENT Table, It doesnt repopulate it with new data (I think)

So should i create a Physical Collumn? As I’m using the Quick update system

Seems that’s not the error, the error is that it’s not linking to the “Parent ID” when creating a new form.

See here :

Capture d’écran, le 2022-08-04 à 10.42.55.png
Capture d’écran, le 2022-08-04 à 10.42.55.png2038×752 119 KB

Here’S the code i’m using, I find it weird, because the LINKTOFORM By itself does work no problem in Deployed version

I ended up swapping the ISNOTBLANK with ISBLANK, Switching to results, and… it works now !! yayyy !!

here’s the finished code if anyone ever needs :

IF(ISBLANK([Parent ID].[Parent_LastChildCollumn].[CollumnToBeChecked]), 
	LINKTOROW([Parent ID].[Parent_LastChildCollumn], "Child_Form")
,
	LINKTOFORM(Child_Form,Parent ID, [Parent ID])
)

I must have had a comma missing or something. Weird how frustrating it can be while being so satisfying ! Thanks SO MUCH for all your help

2 Likes