Forum Discussion

RoulaHabbal's avatar
RoulaHabbal
Qrew Cadet
7 months ago

Looking up a Field in a Different Table in the Same Application without a Relationship.

Hello:

I have one application with many tables. The two tables in question are:

  1. Applications
  2. Student Demographics.

Applications Table Fields and Types:

  • Name (formula-text)
  • Phone Number (formula-text)
  • Email (formula-text)

Student Demographics Table Fields and Types:

  • Name (text)
  • Phone Number (number)
  • Email (email)

Values

  • Name Field ID: 7
  • Table Alias: [_DBID_STUDENT_DEMOGRAPHICS])
  • Table ID: btn82xxxx

Goal: I am trying to look up the name, phone, and email fields in the Student Demographics table and transfer the data to the Applications table (without a relationship) using a formula.

Challenge: I am receiving different error messages.

  • "Expecting text but found number."
  • "Expecting text but found record list."
  • GetRecord(7,[_DBID_STUDENT_DEMOGRAPHICS])
  • GetRecord(7,"btn82xxxx")

Questions:

  1. Am I using the correct function?
  2. Is the Record ID the same as the Field ID?
  3. Am I correct in selecting a formula-text field type for the phone number and email fields?

Thank you,

Roula

formulas
  • JeffPeterson1's avatar
    JeffPeterson1
    Qrew Captain
    7 months ago

    Hi Roula,

    Record ID and Field ID are 2 different values.   Record ID is the entire record # and Field ID is the identifier for the field that value is stored in.

    You need to add GetFieldValues to return the values from record #7 in this case:

    GetFieldValues(GetRecord(7,[_DBID_STUDENT_DEMOGRAPHICS]),XX)

    Where XX is the field ID of the field you want to return the values from.  

    You may also need to wrap this whole formula in ToText:

    ToText(GetFieldValues(GetRecord(7,[_DBID_STUDENT_DEMOGRAPHICS]),XX))

    It seems that you probably also need to make a variable to get the correct record ID from your student demographics table.

    Everything else should work,  your field type is correct and this should be the correct function.


    You're almost there!

    • RoulaHabbal's avatar
      RoulaHabbal
      Qrew Cadet
      7 months ago

      Hi Jeff:

      Thank you for answering my questions and your willingness to help.

      Since 7 is my field ID, I would move it to the end of the formula, correct? 

      GetFieldValues(GetRecord(XX,[_DBID_STUDENT_DEMOGRAPHICS]),7)

      When a student enters his/her ID on the Application table, I want the formula to look at all records in the Student Demographics table.

      I will keep working on this.

      Thank you,

      Roula

      • JeffPeterson1's avatar
        JeffPeterson1
        Qrew Captain
        7 months ago

        Right, you are almost there.

        The part you now have as 'XX' needs to be the Record ID of the record you want to get the field values from.    If the Student ID is the related Student in the Student Demographics table, that would work and return the values from field ID 7 from that specific record in that table.