4 Replies Latest reply on Aug 12, 2014 6:18 PM by YoshioOgata

    Marge two tables' date fields and use it in a Value Lists entry, Dynamically?

    YoshioOgata

      Title

      Marge two tables' date fields and use it in a Value Lists entry, Dynamically?

      Post

                Or How to convert list function's result to Value Lists?
                 
           Hello and thanks everyone sharing great ideas and solutions.
            
           I am trying, without success, to use list function’s results into Value Lists.  I am appreciated if you share some knowledge or guide where I should do.
            
           Here is what I am doing;
            
           1) I have two individual tables, each of which contains unique date (in a date filed), sampling numbers and contained date are irreverent on each table.
           2) What I want to do is to marge two tables' date fields and use it in a Value Lists entry.  
           I want it to be updated dynamic, such as when new date entry is made, the Value Lists be updated accordingly, not every-time importing another table's field or performing scripts...
            
           3)What I came up with is to make use of List() function to get each field's contents as text, then to concatenate each, thus producing what I am intending.  
            
           This far I have come and stuck and need your guidance...
            
           what I want to do next are;
           4) How to convert this result (return delimited text) in to Value Lists.
           5) Additionally, it is quite helpful, if I can sort date.
            
           my working environments:  MacOSX 10.93, filemaker pro 13 advanced
            
           Thank you for your attention.
            
           Yoshio, Kyoto Japan
            

        • 1. Re: Marge two tables' date fields and use it in a Value Lists entry, Dynamically?
          philmodjunk

               You can't do what you are attempting. The List function, by definition will be unstored and thus will lack the index needed to serve as a source of values for a value list.

               You really need to get all of these dates as indexed fields in a single table.

               To gain more assistance, you'll need to describe what you are attempting to do and why in much more detail. Then others may be able to suggest a way to get the end result that you want.

          • 2. Re: Marge two tables' date fields and use it in a Value Lists entry, Dynamically?
            YoshioOgata
                 Thank you (again) for your guidance, Phil.
                 Why the list function result cannot be used in value lists became clear.
                  
                 I will explain what I want to do with more detail.  Please look into the picture I enclosed here;
                  
                 1) I am making an invoice layout which contains both bills-protal and payment-portal; according portals contain a field (moth indicator) for either due month or pay month.
                  
                 2) The records of both bills and payments-table will grow quite large ‘cause there are some 300 parents paying for the monthly admissions and material fees for their children [this db is for an elementary school].
                  
                 3) On the Invoice layout, I want to narrow down to a certain month to calculate its balance (including past transactions).  So I placed a pull down list whose list values are taken from bills-table.
                  
                  But the problem is that bills-table not always contains the same range of the month data as payment-table (because they are independent) does.  There is a situation, for instance, on a certain month, bills are placed and eventually became due but no payments are maid at all.  On the other situaion, some parents would pay in-advance via paycheck, before bills are maid, etc…
                  
                  I want make sure all the transaction be covered form the pull-down, so I needed to combine the both date of bills and payment-table to get all the records of transactions, then sorted it and got rid of the duplicates. 
                  
                  As I have posted earlier, I thought it had been convenient to make use of List() function as this function dynamically pulls all the field values, but as Phil suggested this values cannot be indexed, thus not feasible.
                  
                 4) This is what I generally want to do and I hope it will explain.  As I said before, I would like to avoid importing and combining both fileds if able, but setting up an exclusive combine table and exporting the filed every time transactions are maid may seemingly be good idea.  Also a virtual list may help this case? I will look into it.
                  
                 5)here’s bills and payments tables’ main structure;
                  
                  <<bills table>>
                  __bills_id_pk (text)
                  _invoid_fk (text) 
                  _familyid_fk (text)
                  nameOfBill (text)
                  billingDate (date)
                  amount (num)
                  dueMonth (calc)
                  
                   <<payments table>>
                  __pay_id_pk (text)
                  _invoid_fk (text)
                  _familyid_fk (text)
                  nameOfpay (text)
                  payDate (date)
                  amount (num)
                  payMonth (calc)
                  
                  Thank you for your interest.
                  
                 Yoshio, Kyoto Japan
            • 3. Re: Marge two tables' date fields and use it in a Value Lists entry, Dynamically?
              philmodjunk

                   Unfortunately, I can't read the field labels in your screen shot or see what actual data you want to show in the value list. I can zoom in, but what I then see is blurry.

                   On option occurs to me:

                   Set up a table separate from these where you have one record for every possible "Date" you might need to enter into this field. This can be a table that a script updates once a night to add another date record to the table and which deletes dates that are now in the past.

                   Then set up a calculation field in your invoice table with an expression such as:

                   List ( List ( Bills::Dates ) ; List ( Payments::Dates ) )

                   To get a list of all dates related to the current invoice--assuming that this is what you want.

                   Then use a relationship such as:

                   Invoices::cThisListCalculation = TableOfDates::Date

                   This relationship will match only to records in TableOfDates that appear at least once in the list of dates in the calculation field. The fact that this field is unstored will not keep this relationship from working.

                   You can now set up a condiational value list to list dates from TableOfDates with an "include only related values starting from Invoice" option specified in this value list setup.

              • 4. Re: Marge two tables' date fields and use it in a Value Lists entry, Dynamically?
                YoshioOgata

                     Dear Phil, THANK YOU!.

                     >To get a list of all dates related to the current invoice

                OH yes! This is exactly what I want and, I understand, this is a simplest way to get there.

                     I tentatively prepared some sample data for the TableOfDates and set up the relationship you suggested, it simply WORKS!.

                     I assume ListCalculation field on  the Invoice table still does not index values but since the Invoice is A PARENT table it will not prevent the relation from working.

                     It works beautifully.

                     MANY MANY THANKS

                     Yoshio, Kyoto Japan