lookup a field from last related record?

[Alex Quinlan]

Hi,

 

I am trying to automate entry into the field "Already billed" to be used as part of another calculation.

 

I need to either sum a field of all related records or lookup a field of the last related record.

 

Im sure this must be quite easy, I just don't know how to do it.

 

Any help would be appreciated.

[eos]

I need to either sum a field of all related records or lookup a field of the last related record.

 

Sum ( TheOtherTable::amount ), or 

 

Last ( TheOtherTable::fieldToLookup )

 

Note that last refers to the last record per the relationship's sort order.

[Alex Quinlan]

So this isn't working - I think its because I want to look up from the same table.

I've tried using the summary type of field but I cant find a way of selecting a selecting the projectID as a method of grouping the invoices.

 

 

A working example:

 

This if for an invoice layout which calculates the fee based on project value and stage of billing.

 

Project value £100, agreed fee is 10% of value = £10

On this invoice I want to bill 10% of the total fee =  £1

 

Next invoice the value of the same project has gone up to £120, agreed fee is still 10% of value = £12

I want to bill 20% of total fee = £2.4.

So £2.4 - £1 (because i've already billed that much) = £1.4

[eos]

So this isn't working - I think its because I want to look up from the same table.

I've tried using the summary type of field but I cant find a way of selecting a selecting the projectID as a method of grouping the invoices.

 

You said "related records"; and this makes sense since to calculate the next billing amount and create an invoice based on that data, you would sum up the invoices from the context of their project.

 

If you would to do it from the context of the invoices (not that is a good idea IMO, not to mention that a new project doesn't have any invoices), you didn't need to group the invoices by projectID, but merely find the invoices of a given projectID for a Total summary field to show the correct value.

 

To your other question: as I see it, looking up the last (or better: most recent) invoice wouldn't give you any valid information about the total billing status of the project.

[Alex Quinlan]

Thanks EOS.

 

As you said it makes more sense to keep the data on the projects page in a calculation. I then used a lookup function on the invoices so it wont change in the future.