I'm working with my IT team who is trying to ...

[Tammi_Canelli]

I’m working with my IT team who is trying to build some SQL views for me for various calculations on data - many of these work great but we ran into an issue with one.

I have a table that is simply a list of our evac centers (name, address, capacity, status).

The list is used in a registration table with a valid if statement that only shows the evac centers with a status of “open”.

all works great.

In the view, IT wants to link the two tables based on an ID number for the evac center so we added an ECID column to both tables.

I created a ref from the reg table to the evac list table but I can’t get the ECID number to record in the reg table when someone completes the registration form.

Do I need a ref field to do this?

Is there a better way to record the evac centers ID number to the reg table when someone registers?

[Brian_Sabino]

I’m not sure I’m completely follow, but the AppSheet model maps fairly directly to a relational database (fun fact our CEO used to work on SQL Server).

Your database primary key columns should map to AppSheet key columns, and your foreign key columns map to AppSheet Ref columns.

In general, yes, you should be using Ref columns to link tables.

[Aleksi_Alkio]

What do you actually mean with this one… “I can’t get the ECID number to record in the reg table when someone completes the registration form.”?

[Tammi_Canelli]

Gen Pop Evac Center Reg table ID set as primary key and has two tables (pet and additional household members) that are the child tables referenced to this main table via the ID column.

The EvacCenter column uses a valid if expression to pull the list of centers from the Gen Pop Evac Center List.

Now IT wants to use an ECID column to link the two (for the SQL views) but display the actual evac center name not the ECID number in Appsheet.

I added a VC to ref to the Evac Center List table thinking that would record the ECID number but it didn’t.

If I restrict entries to just the names on the evac center list and make this column a required field so the user has to choose a location can I just eliminate the ECID col?

Everything was working til they had me add that col.

[Tammi_Canelli]

@Aleksi_Alkio this photo might better explain - each shelter has an ID number, when the user fills out the form and selects the shelter we just want the associated ID number for that shelter to populate the field:

[Tammi_Canelli]

How does the lookup expression work and would that solve my issue?

basically when the form gets submitted i want the system to lookup the evaccenter name in the reg table (from the form just saved) in the evac center list, match the name and return the ECID number for that center. right?

[Aleksi_Alkio]

In generally the LOOKUP expression in your case would look like… LOOKUP([_THISROW].[ECID],Gen Pop Evac Center List,ECID,EvacNecterName)