Conditional Count?

[millmaine]

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.

[comment]

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.

 

[millmaine]

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?

 

[comment]

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

 

[millmaine]

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?

 

[comment]

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