I have a table "Schedules" which shows related records from "Line Items".

When viewing a Schedule there is an "Item Count" field which shows how many Line Items are in that Schedule.

Each Line Item is marked with simple Y/N Radio button when Invoiced.

I would like to have a field on the Schedule that shows how many of the related Line Items have been invoiced.

I can do it with a script but would prefer a calculation if possible...but I just can't work out how.

Well, you could make the calculation = 

ValueCount ( FilterValues ( List ( LineItems::Invoiced ) ; "Y" ) )

However, I would advise you to make Invoiced a Number field and use the values of 1 and 0, or just 1 and empty (using a checkbox instead of radio buttons), to populate it. Then you could simply sum or count the field directly.

 

Thanks.

Did as you suggested and made "Invoiced" a Number field (with checkbox ) and then used Sum.

It works, however I find that I sometimes need to click in the Sum field to force it to show the updated calculated amount.

Is there a reason why it doesn't always automatically refresh when the Invoiced field in related record is updated?

 

If you are on a layout of Schedules, with a portal to Line Items, the field should update when you commit the record (i.e. press Enter or click outside of any field).

 

Thanks again. You're right it works fine when using a Schedule layout with a portal to Line Items, however the updating issue occurs if using a Line Item layout with the "Item Count Invoiced" field in a sub-summary by Schedule. Any tips for this?

 

I would suggest you use a summary field defined in the Line Items table (which you can also place on a layout of Schedules).