1 Reply Latest reply on Jan 20, 2014 6:45 PM by erolst

    Get record value from related table

    jmcondon8015@yahoo.com

      Hello,

       

      I created a Parent Table of employees with a unique id (userid). Next I created a child table of devices linked by username with a unique id of serial number.

       

      On the Parent table, I wanted to create a calculated field that:

      1) looks at the device table,

      2) checks to see if the username has a device type of "cellphone"

      3) returns the phone number value from the device table to the cell phone field on the employee table

       

      I used a caluclated field that returns the cell phone number if the type matches. The problem I run into is if the cell phone is not the last device in the device table, it returns the false response.

       

      If(Devices::Equipment Type="Smartphone";Devices::Phone Number)

       

      Any advice would be appreciated.

        • 1. Re: Get record value from related table
          erolst

          Since FM doesn't let you filter related records on the fly, you have to use other methods to get those values:

           

          1. Define a filtered relationship

          and create a calculation field or display the related records in a portal

           

          2. Use a filtered portal

           

          3. Define a calculation using ExecuteSQL

           

          4. Use native functions


          Here's an example file showcasing all techniques, using a global field as selection tool instead of using a hardcoded value ("cellphone").

           

          Note that #4 can read in all related values recursively, without using a Custom Function (grazie, ChinoguMaker …). Have fun checking it out!

          jmcondon8015@yahoo.com wrote:

          I created a Parent Table of employees with a unique id (userid).  Next I created a child table of devices linked by username with a unique id of serial number.

          Not sure I understand this part; you should only use serial numbers as relational keys, not any user-definable data (also not in combination with an ID).