Jump to content

Conditional formatting of invoice line item data using found set


This topic is 2746 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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 by comment
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2746 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.