2 Replies Latest reply on Mar 29, 2013 9:04 AM by philmodjunk

    Finding the first created record

    jconner99

      Title

      Finding the first created record

      Post

           I have a database with two tables the have a one to many relationship. There can be multiple children for each parent. The data is imported from an external source (excel). One of the field in the child is the date the record was created (not in FM but in the original source).

           I have another table that does a calculation that is dependant on knowing the first creation date of a group of children (again this is a date that comes from the original source).

           How do I create a field in my calculation table that populates with the child record with the earliest creation date?

        • 1. Re: Finding the first created record
          sparrow_design

               One approach would be..As Script Steps

               1. Import data into FM from Excel into Child Table.

               2. Isolate one group of Child Records ata time - maybe a Loop of some kind

               3. SORT the Records by the Creation Date (earliest at top)

               4. Move to First Record, mark that Record with a 1 in new number field called 'z_first'.

               5. Back in Parent Calculation Table add global field and store it as Calculated Number = 1, called 'z_one__cn'.

               6. Create the relationship between Calculated Parent Table and Child Table that includes 2 predicates:

               - ID_k_Parent = id_f_parent (will link to group of Child Records)

               - z_one__cn = z_first (will limit to only one within group of Child Records)

               7. In your Calculated Table, you can now add an unstored Calc. Field that uses the relationship to 'see' the TimeStamp of the only Record within Child Group that includes a 1 in the 'z_first' field.

               Let me know if this does not work, or if you need somehting a bit different. Cheers,

               -David

          • 2. Re: Finding the first created record
            philmodjunk

                 defined in the Parent table: Min ( ChildTable::CreationDate )

                 will return the earliest creation date for the set of related child records.

                 a "minimum" summary field in the ChildTable, when referenced from the context of the parent table, will also return this same value.