8 Replies Latest reply on Jul 9, 2013 2:44 PM by MichelleBenes

    Populating a field in a related table based on a condition

    MichelleBenes

      Title

      Populating a field in a related table based on a condition

      Post

           Hello,

           I'm hoping there's a relatively easy way to do this, but I haven't been able to so far.

           I'm creating a database of training courses. One requirement is that we keep a history of price changes for the courses. I have a table for the Courses, and a related (1-to-many) table of Course List Prices. The Course List Prices table contains the following fields:  Course ID, List Price and Date.

           How can I retrieve the current list price (the list price with the latest date) to populate a field in another table?

           One idea I had is to use the "GetNthRecord" function, but I would need a way to ensure that the current price is the first one. Is there a way to sort the records by the Date field when doing a calculation?

           Thank you,

           -Michelle

        • 1. Re: Populating a field in a related table based on a condition
          gcatnine

               I suppose that you use FM12, otherwise…

               The solution is to use the ExecuteSQL:

               ExecuteSQL ( "SELECT Price FROM Table_Course WHERE Date = ?" ; "" ; ""; ExecuteSQL (" SELECT MAX (Date) FROM Table_Course ";"";"" ) )

               But unfortunately it does not work because the second SQL query returns the date in the format yyyy-mm-dd and the Date in the WHERE clause is in the format dd-mm-yyyy.

               This is for sure a bug (I will report it)

               So, do it in this way:

               let ([

               max_d =  ExecuteSQL ( " SELECT MAX (Date) FROM Table_Course ";"";"") ;

               max_d2 = Right(max_d;2) & "-" & Middle( max_d; 6; 2) & "-" & Left(max_d;4);

               p = ExecuteSQL("SELECT price FROM Table_Course WHERE Date = ?" ; "" ; ""; max_d2) ];

               p)

                

               Now it works

                

                

          • 2. Re: Populating a field in a related table based on a condition
            RickWhitelaw

                 Usually the method is to create a Rates Table and have your Course list Prices table lookup the price in the rates table. The history is then preserved in the original table (Course List Prices). When the price for a course changes, update it in the Rates table. The new price will only apply to records you create after this. Lots about lookups in the online help.

            • 3. Re: Populating a field in a related table based on a condition
              MichelleBenes

                   Gianandrea, I've tried your solution and it's returning blank. Do you know if there's a way for me to test/debug what I have?

                   Rick, I'm not sure I understand your suggestion. I still don't see how to make sure to lookup the most recent rate/price.

              • 4. Re: Populating a field in a related table based on a condition
                ninja

                     You would have only one rate record per course...not the many records in your current structure.

                     The lookup would have only one record to look up from.

                     The historical rates would be accesible in the course table since each course would have looked up the rate from the rate record in existence at the time the course record was created.

                • 5. Re: Populating a field in a related table based on a condition
                  philmodjunk

                       Actually, you can have many rate records for the same course in the same table. The trick is to sort the relationship in descending order by a date field in the table that records the date when the rate goes into effect. Sometimes, you then have to filter that a bit more to omit future dates, but this still allows for a relationship where the first related record is the one you want and then standard auto-enter options access the correct related record.

                  • 6. Re: Populating a field in a related table based on a condition
                    MichelleBenes

                         PhilModJunk - That's exactly what I'm trying to do. Can you tell me how to sort the relationship in descending order by the date field so that I can just select the first one?

                    • 7. Re: Populating a field in a related table based on a condition
                      philmodjunk

                           Open Manage | Database | Relationship.

                           Double Click the relationship line to open a dialog box of relationship details.

                           Click the Sort option for the pricing table.

                           Select your descending sort order.

                           As long as you don't enter a price record for a price that has not yet gone into effect, that's all you need do. For cases where you create the records before they go into effect, you need to use an unstored calculation field with Get ( CurrentDate )  as an additional match field matching with an inequality operator to the effectiveDate field as an additional pair of match fields in the relationship:

                           CoursesSelected::CourseID = CourseRates::CourseID AND
                           CoursesSelected::cToday > CourseRates::EffectiveDate

                      • 8. Re: Populating a field in a related table based on a condition
                        MichelleBenes

                             That worked! Thank you!

                             I knew there had to be an easy way.