Thanks for your interest!

 

Trying to figure out a clean and solid way to make a value list of only invoice with an "unpaid" status.

In invoice table I have a calculation field for status. Status field references payment table, if amount payments = amount due; "Paid"; "Due".

 

So, since the status field references a related table, it appears it can not be indexed which breaks the value list.

 

I could always base the invoice status calculation off say, a date paid field. But if i do that, then now I have to enter a payment record, payment record date, and also enter a payment date in invoice record. Just trying to keep extra steps/double entry down of course.

 

Any ideas on a method to fix this issue? I'm open to any ideas and re-work.

THANKS!

Where (in which table/s) do you want to use this value list?

Both the InvoiceTable and the PaymentTable.

BUT... PRIMARILY i would like to use this value list in the payments table.

 

So to enter a Client payment, User would go to Payments.

User would choose Client from value list (only clients with "due" invoices would populate.

 

Does that help?

Oh, when entering payment directly in Invoice table, I would not need a value list of clients/accounts "due".

Payment records would be created by direct relation, etc... different scenario.

 

Most interested in setting up value list of accounts "due" to make payment in payment table.

 

Thanks!!!

I am afraid I am more confused than before. First you said:
 

a value list of only invoice with an "unpaid" status.

 

Now you're talking about:

 

value list (only clients with "due" invoices would populate.

Sorry to be confusing.

 

I need to populate a value list of Accounts/Clients with "Due" invoices. 

So I guess it is a two part set up. First I have to properly label invoice record as "due",

then I need to populate a list of accounts with "due" invoices.

The workaround here uses a value list that shows only related records. This is why I asked where will you be using the value list. If it's in the Payments table, then you could do it this way:

 

1. Define a calculation field cDueClientID in the Invoices table =

Case ( Status = "Due" ; ClientID )

2. Define a relationship between Payments and a new occurrence of the Invoices table named AllInvoices as:

Payments::PaymentID x Invoices::InvoiceID

Note the x relational operator (you can use any pair of fields as the matchfields here).

 

 

3. Define a calculation field cDueClientsIDs (result is Text)  in the Payments table =

List ( AllInvoices::cDueClientID )

4. Define a relationship between Payments and a new occurrence of the Clients table named DueClients as:

Payments::cDueClientsIDs = Clients::ClientID

5. Define a value list using values from DueClients::ClientID, show only related values, starting from Payments.

 

 

There's a more elaborate workaround that would allow the value list to be used from anywhere.

 

 

Note that this is likely to get progressively slower as the number of clients/invoices grows. At some point you'll have to switch to a denormalized solution, where the status of an invoice will be stored.

Thank you so much for taking the time to outline that, I will study it.

 

Note that this is likely to get progressively slower as the number of clients/invoices grows. At some point you'll have to switch to a denormalized solution, where the status of an invoice will be stored.

 

If you may, how should I do this?