8 Replies Latest reply on Apr 2, 2012 11:52 PM by GeoffreyMartin

    A value from field based on another field in a related record...

    GeoffreyMartin

      Title

      A value from field based on another field in a related record...

      Post

      Hello all,

       

      I'm having a complete brain-timeout and know this is easy but can't remember how to do it.  The set up is that I have a main table linked to a related table with routes that relate to a starting point selected in the main table.  The possible routes that can be taken populate a value list.  The user picks from one route from that list (via radio buttons).  In the table the routes have numeric IDs (Sensor_Id).  I need to create another field in the main table to return the ID based on the route selected.

       

      Example:

      Starting_Point: MAIN

      Related records appear value list:M8N-Hwy 80N and Main

      M8S-Hwy 80S and Main

      M5N-Hwy 5N and Main

      M5S-Hwy 5S and Main

      User Selects: M8N-Hwy 80N and Main

      (in that record in the Routes Table there is a field Sensor_Id that has the value 123456)

      I need another field used in a web portal expression in the main table to return 123456.

       

      Any help would be appreciated.  I have forgotten how to make this happen.

        • 1. Re: A value from field based on another field in a related record...
          philmodjunk

          I'm not completely sure I can picture the relationship you have. Is it this?

          Main::RouteID = Routes::RouteID

          Where Main::RouteID has your radio buttons?

          If so, you have two options.

          1) You can copy the Sensor_ID from Routes or you can just display it. A looked up value field option on a sensor_ID field defined in Main could copy the value of Sensor ID from the Sensor_ID field in the selected Routes record. This is useful if you need the current value of a field that may change in the future and you don't want the looked up value to change.

          2) You can also just put Routes::Sensor_ID on your Main layout and it will display the sensor_ID whenever a RouteID is selected in the radio button field. IN this option, if you update the value of Sensor_ID, it automatically updates on the Main layout.

          • 2. Re: A value from field based on another field in a related record...
            GeoffreyMartin

            I'm sorry, I was trying to figure out the best way to explain.  Let me try again...

            In the Main table I have the starting point (posts) of the ambulance recorded in a field called Location_at_assignment.

            In the Sensor table I have all of the freeway sensors in the county (1200 records).  I've added a field to that table that is called Posts.  In that table any of the sensors that are close to each post are noted as such tying them to the post.  Each of the sensor records has an ID #, and a location.  The ID is needed for the query in a web portal I've created.  However, The location is easier for identification. Currently, I have a portal in the layout that brings all of the related sensor locations and IDs to the screen based on the Post.  Then the analyst has to look at that list and then select the appropriate ID in another field.  There may be a couple of sensors that would show up, the analyst will chose the sensor that is in the direction of travel for the ambulance to identify the freeway speeds at that time.  What I have works, it's just a bit clunky.

            What I am after is making the ID part happen in the background. The analyst will chose the sensor based on location and then in a calculation field I identify the ID of that sensor.  Does that make sense? I've included a picture of the new layout I have created hoping to hide the ID#s

             

            -Geoff

             

            • 3. Re: A value from field based on another field in a related record...
              philmodjunk

              I read that as this relationship:

              Main------<Sensors   (---< means one to many)
              Main::Location_at_assignment = Sensors::Posts

              Is that what you have?

              This is still not clear:  "Then the analyst has to look at that list and then select the appropriate ID in another field."

              What other field? Why?

              Isn't the ID field for each related sensor record listed in the portal?

              What happens when the anlyst makes this "selection in another field"?

              Does this update the query URL for the web viewer that maps it?

              If so, why not format the Portal row as a button so that clicking the portal row updates the Web Viewer with the needed info in order to correctly map it?

              • 4. Re: A value from field based on another field in a related record...
                GeoffreyMartin

                A picture is worth a thousand words, The screenshot I posted before is what I would like to happen.  What is below is what is currently happening.  

                The relationship is as you described.

                The Sensor ID is then inserted into a URL query with a bunch of other relevant data that generates a report.

                What I would love to do is select the closest location in one layout (MAIN Table), (which would be a single record of the few related records from Sensor Table) and have FM pull the unique ID from that same record (sensor table) into a field in the MAIN Table that is then used in the expression that creates the URL query?

                If I've lost you completely, I'm sorry to waste your time.

                • 5. Re: A value from field based on another field in a related record...
                  philmodjunk

                  What I would love to do is select the closest location in one layout (MAIN Table), (which would be a single record of the few related records from Sensor Table) and have FM pull the unique ID from that same record (sensor table) into a field in the MAIN Table that is then used in the expression that creates the URL query?

                  I'm assuming that the analyst is deciding which sensor is "closest" here.

                  You can make the buttons in the portal row a single button. Clicking the button can perfrom a script to do what you want here.

                  If you want to save this selection as the "designated sensor" for this record, you can use a set field step such as:

                  Set Field [Main::SelectedSensorID ; Sensors::SensorID]

                  Your webviewer can then use a calculated URL that includes this field in order to map the selected sensor.

                  If this is strictly a temporary selection and you don't want to save the previous selection, you can use

                  Set Variable [$$SensorID ; value: Sensors::SensorID]

                  and your URL calculation can refer to $$SensorID instead of the data field.

                  • 6. Re: A value from field based on another field in a related record...
                    GeoffreyMartin

                    Okay, so that's an option.  What about this one (and is it bad FM voodoo to do)?  (It woke me up this morning)

                    What if I set up another instance of the Sensor table in the relationship graph that relates the Sensors::location to Main::location.  The analyst chooses the closest most appropriate sensor (by location) and the ID is pulled in from the 1:1 relationship (as opposed to the 1:many relationship that already exists but is necessary for the choices).

                    Are multiple instances in the relationships chart bad form?

                    The sensor table is mearly a list of data points.  It shouldn't be modified and isn't available for view by the analysts.

                    -Geoff

                    • 7. Re: A value from field based on another field in a related record...
                      philmodjunk

                      It's very rare that you don't have at least a few multiple occurrences of a given table in FileMaker, it's the only practical way to get more than one relationship between the same two tables. You may want see this article to learn more: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                      I'm not sure, however, how that would work for what you want here. Aren't you searching for sensor locations that are "close to" the starting position of your emergency vehicle? If so, then in the vast majority of cases, none of the sensor records will match to the starting location exactly and thus will not appear as a related record when matching by location.

                      I'm not sure what this statement has to do with the details of what we've been discussing:

                      The sensor table is mearly a list of data points.  It shouldn't be modified and isn't available for view by the analysts.

                      I've assumed from the outset that what you want here would not involve modifying data in the sensor table.

                      • 8. Re: A value from field based on another field in a related record...
                        GeoffreyMartin

                        So here's what I did.  I had a field Main::Freeway_Sensor_list that had a value list with the Sensor::Locations related (----<) to the Main::Starting_Location set up as radio buttons.  This filtered down the list of 1200 to the 4-6 most appropriate sensors.  The analyst chooses the closest sensor in the path of the ambulance with the radio button.  I then set up another instance between the two tables that links Main::Freeway_Sensor_list to Sensor::Location.  This takes the single location and then pulls the sensor::ID into the layout in the Main table and query expression. Works like a charm!  Thanks for the help.