8 Replies Latest reply on Oct 8, 2013 7:19 AM by philmodjunk

    How to have layout compare two different fields

    danlee

      Title

      How to have layout compare two different fields

      Post

           I have a layout with a portal that contains data for fields from different dates consisting of, Weight, Chest, Waist, Hips, Arms and Thighs.

           I want to be able to have a script run to compare these fields on another layout.  I would like to be able to choose between 2 different dates and have those data appear in the Link 1 picture down below.  I have been struggling with this for the longest time.  Please any help would be appreciated.

      Link 1

      Link 2

        • 1. Re: How to have layout compare two different fields
          philmodjunk

               Before we can discuss how to "compare fields on another layout" we need to talk in terms of records and tables since the data you see on a layout comes from one or more records in one or more tables. How you structure these tables and link them in relationships is key to how to compare a set of measurements maid on two different dates.

               What tables have you defined for this data? How are they linked in relationships?

               Do you have one table for the client linked to a measurements table such that you can record many different sets of measurements each in a different record linked to a specific client?

          • 2. Re: How to have layout compare two different fields
            danlee

                 Sorry been away...

                 I have 2 tables, Measurements and Measurements2.  They contain all the client stats.  I have another table named Clients that both Measurements tables are linked to.

                 "Do you have one table for the client linked to a measurements table such that you can record many different sets of measurements each in a different record linked to a specific client?"  Yes

            Screenshot of table relationship

            • 3. Re: How to have layout compare two different fields
              philmodjunk
                   

                        I have 2 tables, Measurements and Measurements2.

                   Hmm, well that's actually two Table Occurrences named Measurements Data and Measurements Data2...

                   You have two different options that can work here: You can set up two date fields in Clients and by putting different date fields, use two new occurrences of Measurements Data with relationships similar to that for Measurements Data 2 but each with a different date field used as the match field. You can then select different dates in the two date fields to compare a different measurements data record for that client.

                   You can also use two portals to Measurements Data with a portal filter that compares a date field in client to a date field in the portal's table. You can then select dates in date fields to compare measurements in this manner. This keeps your relationships graph simpler but has some limitations in what you can do beyond simply displaying the data from the two dates.

                   But if you intend to use these measurements in calculations such as computing a difference or a percent change, use the first option.

              • 4. Re: How to have layout compare two different fields
                danlee

                     I have a layout that this is what I want it to look like.  It would pull up data from different dates and compute the change between the two.

                Data Summary Layout

                • 5. Re: How to have layout compare two different fields
                  philmodjunk

                       In order to get the values in the 3rd column, you will need to use the first option that I described.

                  • 6. Re: How to have layout compare two different fields
                    danlee

                         Hi Phil,

                         Confused on how to set this up.  So option one will be the way for me to go.  If you look on this layout all the data right now are being pulled from MeasurementData2:  Screenshot

                         So I need to create 2 date fields in Clients table but how do I match up the relationship with the data from the MeasurementData2 table?

                    • 7. Re: How to have layout compare two different fields
                      danlee

                           This is how I have it set up...

                      Screenshot

                           not working :(

                           I want to be able to use this layout to choose the 2 different dates and run a comparison showing on another layout...

                      Screenshot2

                      Screenshot3

                      • 8. Re: How to have layout compare two different fields
                        philmodjunk

                             Apologies, some of my posts have gone missing on me. I may have replied to the wrong thread on a different browser tab or we may have some gremlin in the works.

                             

                                  So I need to create 2 date fields in Clients table but how do I match up the relationship with the data from the MeasurementData2 table?

                             to repeat, Measurements Data 2 does not appear to be a separate table, but a table occurrence with the same data source table as Measurements Data. This is a key distinction to keep in mind.

                             I don't know what type of fields you have named BodyAgeDate or GMDate but they appear to be date fields. Using two pair of match fields, one for matching dates and the other pair for matching client ID's are what you need here. You will need two table occurrences of your Measurements data and a different date field in client as the date match field for each. Whether your existing relationship to Measurements Data 2 can be used as one of these is something that I can't tell from here, But with those two relationships in place, you can set up a layout based on clients that lists the measurements from both of these date specific relationships and calculation fields defined in Client can refer to data in these two table occurrences to compute the comparison values.