Restrict Value List

[Stickybeak]

I know this is simple. I know that I have seen a post about how to do it in my travels around this marvellous resource.

I have a database called matters. I have a time record database called timselips.

In timeslips I have a drop down list of matters which enables me to easily enter matter names for recording time against them.

Eventually matters finish and i have a procedure for marking them inactive.

i would like to arrange my drop down list in timeslips so that when a matter becomes inactive it no longer appears in the drop down list.

[comment]

Try defining a calculation field in the Matters table =

Case ( not Inactive ; Matter )

and base the value list on that.

[Stickybeak]

Try defining a calculation field in the Matters table =

Case ( not Inactive ; Matter )

and base the value list on that.

It wont let me create a value list from a calculated field.......

[Stickybeak]

It wont let me create a value list from a calculated field.......

My solution is this: I have added a step to my finalise matter script which clears the field I was using to lookup in the time recording file. This means that the record in matters no longer has data in the lookup filed and therefore does not appear in my drop down/ value list.

There may be more elegant solutions and I would love to hear them.

[comment]

It wont let me create a value list from a calculated field.......

Only if the calculation is unstored - and I see no reason why it cannot be stored in this case.

[Stickybeak]

Only if the calculation is unstored - and I see no reason why it cannot be stored in this case.

and which is the better solution?

[comment]

I could answer that only after understanding yours ... :P

[Stickybeak]

I could answer that only after understanding yours ...

Well I've adopted your and its working like a treat - thanks.

But here's another issue along the same lines: I have invoices showing in a portal on matters. There is a total for all invoices sent in a matter. I do the same for payments. I deduct the total payments in matter from the total of invoices to find out how much is owing.

when i go to the payments database to record a payment I would like to have the invoice numbers of only those invoices which are yet to be paid - so that i dont have 10s of invoices - which increases the risk of a data entry error I reckon.

The total for the invoices (and payments) is "Sum(invoices::grand_total)": no matter what I do I cant seem to tweak this formula so as to permit storage.

[comment]

I would like to have the invoice numbers of only those invoices which are yet to be paid

I am not sure I understand your question. It you record payments against a matter, then there are no paid/unpaid invoices - only matters that have an outstanding balance, and matters that are (at this time) settled.

[Stickybeak]

I am not sure I understand your question. It you record payments against a matter, then there are no paid/unpaid invoices - only matters that have an outstanding balance, and matters that are (at this time) settled.

No - you did understand the question and your reply highlights the flawed approach I was using.

So I reconstructed payments so that the lookup field was the calculated field you first suggested so that only active matters were offered to complete the field. I then had a second field that dropped down to show invoices related to the matter and the balance of each.

Thanks for your help.

:D