Conditional formatting of invoice line item data using found set

[macmasterservices]

We have database which shows line items from the Line Items table in a portal on an invoice form from the Invoices table, and two of the fields in each line item are Staff Initials and Date.

 

Because our invoices are for time billing, we do not want any particular staff member to show more than 10 hours for any particular date across all invoices in our system. 

 

For this reason, we want to use conditional formatting to have the date show up in red if that staff member has more than 10 hours billed on that date. 

 

I am looking for some tips on how to implement this. I believe it would require opening the line items table and using Find commands to search for any line items with the Staff Initials and Date in question, then totaling up the hours on the found set. Beyond that I'm lost and looking for guidance. I'm also open to any other solutions, such as a separate (non-printing) field in each line item that simply shows the total hours for that Staff/Date combination.

 

Thanks in advance for any input on this.

[webko]

If I were to do this (and there have been times when I have worked more than 10 hours a day on billable projects...), I would probably use a self-join in Line Items table on Staff Initials and Date, plus a calculation field that is set to 1 where the Sum of hours across the relationship is > 10, then use the Conditional Formatting to check that field.

[comment]

 

1 hour ago, webko said:

plus a calculation field that is set to 1 where the Sum of hours across the relationship is > 10,

There's no need for a calculation field. The formula for conditional formatting can be:

Sum ( LineItems 2::Hours ) > 10

(this is assuming Hours is a Number field).

 

--
Note: Contrary to what the title says, this has nothing to do with the found set.

 

Edited November 15, 2016 by comment

[webko]

Won't that give the Sum of hours for this Invoice - I think the OP wanted a given line item to show differently if that Staff Member (in the Line Item), on that Date, had accrued more than 10 hours

[comment]

31 minutes ago, webko said:

Won't that give the Sum of hours for this Invoice

I had a typo: it should read Sum ( LineItems 2::Hours ), which will consider all related line items, regardless of invoice.

 

Edited November 15, 2016 by comment

[macmasterservices]

Thanks for the replies. I will dig into this and report back with the results.

[macmasterservices]

On November 15, 2016 at 1:51 PM, comment said:

 

Sum ( LineItems 2::Hours ) > 10

 

What ended up working splendidly was a double self-join of Staff and Date between LineItems and LineItems 2, with conditional formatting as per comment's first post quoted above. Simple and effective, thanks!

[webko]

Awesome...

Just as a slight improvement - always try to make your TO names descriptive of what they are doing / are for - in 6 months LineItems 2 is likely to mean nothing to you

lineitems_SELF_Staff_Date might be one way to auto-document what the TO is for (there are a heap of naming conventions suggested - any are fine, so long as you know what they're documenting, and they're consistent