Need some help please. Have two tables. Tabl...

[Tammi_Canelli]

Need some help please.

Have two tables. Table 1: Gen Pop Evacuation Center Registration Table 2: Gen Pop Pet Registration

Table 2 contains an ID column that is Ref type back to Table 1.

I want to add a virtual column to Table 1 that will show the pet location from Table 2. But when I try the expression below it shows every pet location not just the location for that ID:

Gen Pop Pet Registration[Pet/Crate Location]

How do I filter it by registration ID?

I’m good with references from table 1 to table 2 [ID].[col] - but not the opposite direction.

[Tammi_Canelli]

Almost works…the expression verifies but seems it’s still giving me everything:

SELECT(Gen Pop Pet Registration [Pet or Crate Location],[ID]=[ID])

[Tammi_Canelli]

Thank you both.

Is there a way to get all the pet locations related to the owner (parent) record or just the top one?

I guess the pet teams plan is separate locations for cats and dogs so I’m struggling with the best way to display the info they want in a table view.

[Suvrutt_Gurjar]

@Aleksi_Alkio you are very right.

[Tammi_Canelli]

@Aleksi_Alkio the any select works.

now my question is can I add another condition to this statement?

Since they are separating dogs and cats and I can only get one room number to appear I’m thinking of adding a 2nd virtual column so I can have Dog Location, Cat Location, but not sure how to write it.

This didn’t work:

SELECT([Related Gen Pop Pet Registrations][ Pet or Crate Location], [PetTypeDog]=true, [ID]=[_THISROW].[ID])

[Stephen_Mattiso]

@Tammi_Canelli

YW!

[Lynn]

Hi @Tammi_Canelli maybe try an AND in there. …AND([PetTypeDog]…)

[Tammi_Canelli]

Here is the column data.

Do I need to incorporate the PetID somehow since it’s the key in the pet table?

[Stephen_Mattiso]

Might need ANY(SELECT…

[Suvrutt_Gurjar]

@Tammi_Canelli

Yes, please include [PetID] in your expression. I had mentioned it so to include names of columns in respective tables.

Hi @Stephen_Mattison , Any may not be required as there could be multiple related records and as such the resulting column needs to be list type.

[Suvrutt_Gurjar]

Just to reiterate,

the expression needs to have [name of Key column of table 1] =[name of ref column of table 2]

Also names need to be a bit different, because otherwise,

the system may not understand,

these are columns of two different tables. Hence requested a bit different name for ref column of table 2.

Hope this helps.

[Aleksi_Alkio]

When you already have a list of child records in your parent table (table 1), You could read the correct value like… ANY(SELECT([VirtualRefListField][SourceColumn],[ID]=[_THISROW].[ID])). If there are more than one record with the same ID, then it will show the first one. You need to use your own column names but the format should be fine.

[Suvrutt_Gurjar]

@Aleksi_Alkio, Yours is compact and to the point solution as usual

[Aleksi_Alkio]

For some reason users don’t know the structure SELECT([VirtualList][SourceColumn],TRUE) when the REFROWS is used. It helps because you don’t need to read the whole table again. You have that list already but it’s much smaller… normally just few records.

[Suvrutt_Gurjar]

Hi , Based on my understanding, you wish to refer back [Pet Location] from Gen Pop Pet Registration table into Gen Pop Evacuation Center Registration. You have a reference type column in Table 2 that refers Key column of Table 1.

If this is so, please try with the following expression by creating a virtual column of type list in Table 1

SELECT(Gen Pop Pet Registration [Pet/Crate Location],[Key Name of Table 1]=[Reference Column Name in Table 2 that refers to Table 1])

You may wish to remove / in column name [Pet/Crate Location], as such characters are typically reserved in computer expressions and may lead to errors.

Hope this helps.

[Suvrutt_Gurjar]

Sorry, please ignore [PetID]. Please rename ref column

[ID] as say [IDPR] and please try the following

SELECT(Gen Pop Pet Registration [Pet or Crate Location],[ID]=[IDPR]) Presume [ID] is key nameof table 1. Or please use that key name here.