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.
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
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.
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