0

How do I show information from another table, based on user input?

Hello,

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.

statepermits_details

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!

9 replies

null
    • Danjamesmedia
    • 5 yrs ago
    • Reported - view

    Thanks, Sean.

    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!

    • Danjamesmedia
    • 5 yrs ago
    • Reported - view

    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

      • AHS
      • Alexis_Wilson
      • 2 yrs ago
      • Reported - view

      Dan James (Ninox Partner) Hi Dan, what if the 'State' field was a dynamic choice field also collecting data from the other table?

      • Danjamesmedia
      • 2 yrs ago
      • Reported - view

      Alexis Wilson  

      let rec := record(OTHERTABLENAME,number(StateDynamicChoiceField));
Username := rec.Username;
Password := rec.Password;
Website := rec.Website
    • SNJ ENTERPRISES LLC
    • Jeremy_McCloud
    • 5 yrs ago
    • Reported - view

    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!

    • Danjamesmedia
    • 5 yrs ago
    • Reported - view

    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. 

    • SNJ ENTERPRISES LLC
    • Jeremy_McCloud
    • 5 yrs ago
    • Reported - view

    Good idea!  Thank's Dan James!

    • Sean
    • 5 yrs ago
    • Reported - view

    You can use... record(YourTable, YourId).(YourField := YourValue) ...to assign a value to a field in another table.

    • Danjamesmedia
    • 5 yrs ago
    • Reported - view

    Here it is: http://cl.danjam.es/61f3640696c9

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 9Replies
  • 3050Views
  • 2 Following