2 Replies Latest reply on Jul 3, 2013 7:11 AM by deathrobot

    I have a parent table and a child table. Each parent record has several children. The child contains...

    deathrobot

      Post

           I have a parent table and a child table. Each parent record has several children. The child contains a text field (called "firstRun") that always starts with month day, year (e.g. "January 1, 2013") then has some additional text. On a layout in the parent table, I need to show only the earliest date from the several children. I've solved it, but am certain I've chosen an unnecessarily circuitous method of accomplishing this. Any suggestion on how to make this simpler? Here's what I have:

           A TO of the child table whose sole purpose is for sorting the date field

           In child table, 4 unstored calculations (already a bad sign…)

             
      1.           %firstRunDateDay = GetAsNumber ( MiddleWords ( firstRun ; 2 ; 1 ) )
      2.      
      3.           %firstRunDateMonth = LeftWords ( firstRun ; 1 )
      4.      
      5.           %firstRunDateYear = GetAsNumber ( MiddleWords ( firstRun ; 3 ; 1 ) )
      6.      
      7.           %firstRunDateFull = LeftWords ( firstRun ; 3 )

           The relationship is sorted via the graph like this:

             
      1.           %firstRunDateYear (ascending)
      2.      
      3.           %firstRunDateMonth (custom order based on a value list that has the 12 months in order)
      4.      
      5.           %firstRunDateDay (ascending)

           The parent table has as single unstored calculation to show the earliest date:

           LeftValues ( 
                List (
                     ChildTable::%firstRunDateFull
                );
                1
           )
            
           So, this works but took a sorted TO, 5 unstored calculations, and a custom value list. Would love to be educated on how to simplify this.
            
           Thanks!
           Michael

        • 1. Re: I have a parent table and a child table. Each parent record has several children. The child contains...
          jmw

               You could create an extra date-field 'firstRunDate' in the child table. Having that would allow you to have a TO of the children sorted on this date, so in the parent layout you could simply take the first available record from this TO.

               The interesting bit is the setup of the date field: make it an AutoEnter-field with the 'Do not replace...' option turned off. This way the field will be updated when the firstrun text-field is updated and otherwise be left alone.

               The auto-enter calculation could be like this:

                

          Let([
          monthAbbr =  Upper(Left ( LeftWords ( firstRun ; 1 ) ; 3 ) );
          month = 
          Case (
          monthAbbr = "JAN"; 1;
          monthAbbr = "FEB"; 2;
          monthAbbr = "MAR"; 3;
          monthAbbr = "APR"; 4;
          monthAbbr = "MAY"; 5;
          monthAbbr = "JUN"; 6;
          monthAbbr = "JUL"; 7;
          monthAbbr = "AUG"; 8;
          monthAbbr = "SEP"; 9;
          monthAbbr = "OCT"; 10;
          monthAbbr = "NOV"; 11;
          monthAbbr = "DEC"; 12;
          0 // default
          );
          day  = GetAsNumber ( MiddleWords ( firstRun ; 2 ; 1 ) );
          year = GetAsNumber ( MiddleWords ( firstRun ; 3 ; 1 ) )
          ];
                
          If (month > 0; 
          Date ( month ; day ; year );    // valid date
          ""                                                // invalid date
          )
          )
                
               I have formatted this hoping you will be able to figure out what is happening here...
               Using this you would not need the other calculations and the value list. The extra child TO would be handled differently; sorted on the firstRunDate field for starters and maybe add an extra relationship between this table and the parent table to exclude empty firstRunDate fields as these would have an invalid firstrun-date.
                
               As a side-note: if there are other places where you would need the monthNumber, you could make a user-function of the case-statement. 
               Let me know if you have any questions. 
          • 2. Re: I have a parent table and a child table. Each parent record has several children. The child contains...
            deathrobot

                 Ahh, thanks so much, Jan. This is much better. I've already changed everything and it works great. Really appreciate the help.

                  

                 Michael