Changing value in parent table based on Related Re...

Presley_Thompso · 03-31-2020 12:30 PM

Heyo.

I’m wondering how to search items that are related to a row, and update a value in that row based on findings in the related items. As an example, if I have a record representing a Project, with related items in a different table representing Tasks with dates, can I set a Status value in the Project based on finding a date value in the related Tasks? So for example if I find a Task that has a due date of Today, reflect that in the Project row?

Thanks!

Prelsey

MultiTech

Yes, absolutely - and it’s even easier because you have the reference set up.

You can use a reverse reference to pull information from the related records. Now, the result is a list… So you have to treat things accordingly, but with this methodology it makes it really easy (and efficient) to pull data from related records.

You could do something like:

If(in(today(), [Related Records][Due date]),
“Due today”,
“”)

Presley_Thompso

Thanks @MultiTech_Visions!

That doesn’t seem to be working for me, and I’m not sure what you mean by “treat things accordingly” with the list result. I’m pretty new to AppSheet, so assume I know nothing . With that formula on the column, do I need to set the column TYPE to something in particular (currently is Text), or any other settings in the column?

Appreciate your help!

Presley

MultiTech

I just meant that you can’t use things like [This] = [That] = becuase [That] is actually a list of things, so you have to use IN() instead; that is all.

For a good start with references, check out this doc:

It all depends on what you are trying to do.

is the criteria to check if today’s date is inside any of the due dates for the related records. From this you can do all kinds of things.

The how depends on exactly what you want to do.

Presley_Thompso

Thanks, I’ll read up. I get the if in formula, and it validates how I’ve written it out in the expression assistant, but the field doesn’t update with true or false (stays blank). So that’s why I’m wondering if it’s a matter of the settings in the column or something. I’ll see how far I can get with that article, thanks.

MultiTech

You might need to separate out the list of [Related Records][Due Dates] into it’s own virtual column, then use THAT in the IF(IN()…

Also, check and make sure there is actually a record with a due date of today - otherwise it will stay blank (but that’s correct!).

Presley_Thompso

Hm. That makes sense, still having trouble. I have a virtual column called “Test: Related Tasks” - and related tasks that have a “Status” column, some of which have the value “COMPLETE”. So this would seem to make sense, correct?

IF(IN("COMPLETE",[Test: Related Tasks]),“Yes”,“No”)

I also tried with your first suggestion referencing the column:

IF(IN("COMPLETE",[Related Tasks][Status]),“Yes”,“No”)

MultiTech

The syntax is like this:

[Name of the related list column in the parent table][Name of the column in the child table]

The text you put inside the square brackets needs to be exactly what you have for the column names:

IF(IN("COMPLETE",[Test: Related Tasks][Status]),“Yes”,“No”)

I think might be more correct.

Presley_Thompso

Thanks. I think I get the syntax, and have it written properly - something else must be off. Here’s a more detailed breakdown, maybe you can spot something: The Parent table is “Cleanse Records” and the related records are in a virtual column called “Related Tasks”. That refers to a table called “Tasks” that has a column named “STATUS”, the values of which are “COMPLETE” or “INCOMPLETE”.

In the Status column of Cleanse Records (parent table) I have this formula:

IF(IN("COMPLETE",[Test: Related Tasks][STATUS]),“Yes”,“No”)

The Expression Assistant validates this, so references are correct. That Status column’s TYPE is set as “Text”, and by default it is blank.

I also tested it another way, with a virtual column named “Test: Related Tasks” which is [Related Tasks][STATUS], and then used this formula instead of the above:

IF(IN("COMPLETE",[Test: Related Tasks]),“Yes”,“No”)

In both cases the parent table Status column with the formula remains blank. What might I be missing? Does the Status column TYPE need to be something else?

Again, appreciate your help!

MultiTech

No, the column type for this expression will be text, I assume you’re building a status column for your projects table.

If this is true - it’s showing a blank - try this:

if(in(
   "Complete", 
   split(concatenate([Test: Related Tasks][STATUS]), " , ")
), 
"Yes", 
"No")

I’m betting the reverse reference isn’t outputting the correct type, this should force it back into a list.

Presley_Thompso

Thanks… still no luck. I’ll search around a bit and see what I can figure out.

Presley_Thompso

@MultiTech_Visions, is there any way I can give you temp access to view my app to take a look (if you’d be willing)? I’ve been searching for what could be going wrong, but haven’t had any luck…

Or perhaps a demo app you know of that might use this reference? I’ve looked at a few that I thought might, but no luck there either.

Presley

Changing value in parent table based on Related Records - need help!