Re: Cannot access parent table when in a START: co...

1minManager · 12-06-2021 09:47 AM

Hi. I have an App with the following hierarchy of tables:

Customer
|
Site
|
Job

In each daughter table the Ref column of the parent has the same name as the parent table. Also each parent table has the standard [Related …] column to access daughter records. The Job table has references to both the parent (Site) and grand parent (Customer) records.

The template runs seperately on each Customer records and the START condition is

<<START: Select([Related Jobs][JobID],AND(...

So its creating a list of jobs. The data I want to access is <<[Site].[Name]>> for each of the Job records called by this START condition. But its not working
<<[Site]>> works fine and returns the Site key column
<<[Site].[Name]>> returns nothing and doesn’t error
<<Any(Select(Site[Name],[SiteID]=[_ThisRow].[Site]))>> Errors and says the site column doesn’t exist
<<[_THISROW-1].[Name]>> returns [Customer].[Name]

I’m presuming this has something to do with the fact that we have the Grand Parent record creatign a list of Grandaughter records. But I can’t work out why I cannot access the parent of a Job record

Obviously I could fix with a virtual column. But the Job table has 16000+ records

Can anyone explain what I’m doing wrong?

Marc_Dillon

Can you show your whole template?

1minManager

This is the entire template. I’ve put in <<[Address]>> for now as this is the data thats nearest to what I want

SkrOYC

Why not this?

<<START: SELECT([Related Jobs]...

Since [Related Jobs] is already a list of key columns, [JobID] seems unnecesary

Marc_Dillon

Because that’s not valid syntax.

SkrOYC

Haha, sorry. I wrote the Select() but ignored it completely! My fault!

SkrOYC

So… have you tried using a FILTER() expression and adding another = to your AND() to pull just the Related Jobs? @1minManager

1minManager

So the START is not really the issue. That’s bringing in the correct rows. The issue is pulling some data, from within those rows, from a parent record. Whats odd is that when I choose <<[Site]>> in the first column of teh template, which is a Ref to the parent table, its clearly pulling in data

Yet <<[Site].[Name]>> doesn’t work.

Koichi_Tsuji

I could quickly manage to repro the problem with my sandbox app. This is obviously a bug, I suggest you formally report and register this as bug through support desk.

@Takuya_Miyai

Koichi_Tsuji

I have open ticket to the support desk, dot operator to pull the related record value in AppSheet webhook JSON body is not working properly. It sounds to me both cases might share the same root cause? Out of pure guess work though…

1minManager

Ok, I’ve reported mine too.

I think you’re right about it being a bug as I have another template which the parent record (Site) uses START to create a list of daughter (Job) records. In that one <<[Site].[Name]>> works fine

Koichi_Tsuji

I bet this is perfect bug.

Marc_Dillon

I agree, kind of looks like a bug. Or at least I don’t see any obvious issues. If it was my app I would simplify the scenario down to test it. Like a template that is literally just:

<<START: LIST( "one-key-value")>> <<[Site].[Site Name]>> <<END>>

Ccuadros

What happens if SELECT() has 0 rows? is there a way to not print the table?

Cannot access parent table when in a START: condition