Invoice Starter Solution Calculation

[Isaac Knoflicek]

I'm using a modified version of the Invoices Starter Solution form FM13 and I've added a boolean field to the Invoice Data table which indicates if it has been ordered or not.

What I'd like to do is add a boolean calculated field in the Invoices table which looks at all related records, all the items on the invoice, and if they've all been ordered it's 1.

If I create the calculated field in invoices and set it to If(InvoiceData::Ordered=1,1,"") it works as long as there's only 1 item.  If there are two or more items it just moves in lock step with the first item and ignores the rest.

Any way to do this without a script?

Thanks,

Isaac

[comment]

A calculation that references a related field gets the data from the first related record only. In order to consider all related records, you need to use one of the Aggregate functions. Try something like:

Sum ( InvoiceData::Ordered ) = Count ( InvoiceData::OrderID )

Personally, I would prefer to make Ordered a Date field, and count it instead of summing it.

[Isaac Knoflicek]

That's brilliant, that should work.  

As far as your preferred method, how would you count a date field?  I do have an ordered timestamp field which just puts in the time when the ordered checkbox was checked.

Thanks for your help!

Isaac

[comment]

48 minutes ago, Isaac Knoflicek said:

how would you count a date field?

Just like that:

Count ( Related::Datefield )

returns the number of related records where Datefield is not empty.

 

48 minutes ago, Isaac Knoflicek said:

I do have an ordered timestamp field which just puts in the time when the ordered checkbox was checked.

I don't think you need two fields for this. A button that populates/clears the timestamp field should be quite sufficient. And you can use conditional formatting to display a tick mark when the timestamp field is not empty.

 

Edited January 6, 2016 by comment