How do I show information from another table, based on user input?
We deal with a lot of websites for logging in and obtaining oversize and overweight permits. We have a seperate table we use that keeps track of all the websites for each state, the username and the password.
Take a look at the attachment for a better understanding.
Here is what I'd like to happen. When the user types the state intials in the "State" field, I'd like to use the "Trigger After Update" feature to use what the user typed, pull up the record information from our username and passwords table and display the state website, username and password in the fields below it (pictured in the image above). I'd then like to be able to let the user change the state website url, username or password and save the information back to the table as needed.
For example, the user types "MD" in the State field. The URL, Username and Password website is then field with the information from another table that match that state inital, "MD". The user can then change any of those three fields and click the "Save" button to save the changes back to that table. ("Save" button isn't shown in the photo)
Records from the other table, which I'm calling QuickLinks has a State Field (State Abrv.), URL Field, Username Field and Password Field.
What is the best way to accomplish what I'm trying to do here?
Thank you so much!
Try the following:
let SearchTerm := State;
'State Website' := concat((select OTHERTABLENAME)[State = SearchTerm].Website);
Username := concat((select OTHERTABLENAME)[State = SearchTerm].Username);
Password := concat((select OTHERTABLENAME)[State = SearchTerm].Password);
Line 1 assigns the state field to a variable called SearchTerm
Lines 2 - 4 assign the field values in this table with the searched term from the other table. You'll need to replace:
OTHERTABLENAME with the name of your table containing usernames, passwords and statewebsites
State in the  brackets with the name of the field that stores the state in the other table.
Will write back once i've figured saving back to the other table
Wow! Thank you so much Dan James! It worked like a charm! If it's not any trouble, please let me know if you figure out how to save any changes back to the table.
I really appreciate this. Thank you!
Haven't figured it out yet although tried for a while. Maybe someone else can help with updating field values in a record in another table with a given Id.
Something to add to the above code is a test to only trigger the retrieval of the values if the username/password/url fields are empty, otherwise once you've changed their values, the on update script will retrigger overwriting your new values! Alternatively use another button to retrieve the values rather than on update.
Good idea! Thank's Dan James!
You can use...
record(YourTable, YourId).(YourField := YourValue) ...to assign a value to a field in another table.
So the formula for the save button is as follows:
let SearchTerm := State;
let i := number(concat((select OtherTable)[State = SearchTerm].Id));
let NewUsername := Username;
let NewPassword := Password;
let NewWebsite := Website;
record(OtherTable,i).(Username := NewUsername);
record(OtherTable,i).(Password := NewPassword);
record(OtherTable,i).(Website := NewWebsite)
First two lines assign the foreign record ID to i
Lines 3-5 assign the local new field values to variables
Lines 6-8 assign those new variable values to the relevant fields in the OtherTable!
Happy to share the test database if that helps!
Here it is: http://cl.danjam.es/61f3640696c9