How to populate multiple fields from a table using...

TheBearDen · 02-23-2021 11:03 AM

I have two apps that I use to track expenses and fuel usage with years of data.

In both of those apps I have five columns:

Location, Address, City/Town, Province and Lat/Long.

Currently I enter the data manually. However, I would like to be able to select and enter just the location and have the other four fields filled in automatically.

My data is there in the same sheet as a table other than the main table, all I need to know is how can I access it an make it work as described.

In the interim, I am using enumlist which allows me to select each field individually.

I think I have to use the valueif function, but am unsure how to do that.

My goals here are data accuracy, consistency, time saving and efficiency.

As well, I would like to be able to add and capture new data as required in those five fields.

Can anyone provide some guidance on this for me?

Any help is greatly appreciated.

Thanks.

P.S. Hopefully I posted this in the correct forum. If not where would be a better spot to post it?

As well, if required I can share the tables etc., but am unsure as how to do that.

Andy

shaaland

Hi @TheBearDen Andy!

Thanks for your question and welcome to the community!

You should be able to accomplish this by referencing the other table that has all of the data about the locations if I’m understanding your use case properly.

Change the data type of the column “Location” to be a “Ref” type
Click on the pencil icon to the left of the column name - this opens the column detailed definition pop up
Select your “other table” where your location data resides in the “Source Table” drop down under “Type Details”. Let’s assume your other table is called “Locations”. This tells AppSheet which table to use for the Ref type you set up in step 1. Think of this as a link to the other table based on the value of the Location column.
Use a formula in your other columns, like Address for the initial value to be something like this:
[Location].[Address]
For City/Town, it would look like this
[Location].[City/Town] (match this to the column name in the Locations table)
repeat pattern for other columns
etc…

Once you have these referenced, then the initial value should be filled in for all the fields once the user selects the location, which should be a drop down on the add screen now that it is a Ref type. If you leave them as Editable, then the user could over ride them with a custom value, but if you want it to always match locations, you can just unclick the “editable” flag and then you should have perfect data integrity.

Hope that helps,

Scott
AppSheet Product

shaaland

I corrected a syntax error above, so the edited version should be good to go now.

Cheers,
Scott

Steve

How to populate multiple fields from a table using one field in a dropdown?