cancel
Showing results for 
Search instead for 
Did you mean: 

Change Text Color Based on Date

former_member967270
Discoverer
0 Kudos
174

I have this pick list report that gives a list of items on an order. On this report we also have multiple tag numbers that will allow us to see which item is the oldest and basically tells us which on to pick. Ive had it sort those tags in each section by the date they were manufactured and that works. What I'm trying to do now is change the text color of that oldest item to red so I can always tell our employees to pick the item in red. I have written a script and it does highlight the oldest in red but it does do it per section. It does it as a whole which means some parts do get highlighed because their date is newer than a date in another section. Below is my script. Can you help me correct this?


If ({PickList.OrderMaterialAvailable__Date_Posted} = minimum({PickList.OrderMaterialAvailable__Date_Posted}))
then crRed else 0

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

Assuming you're grouping your data based on some field and you want the red for the oldest to show for each group, here's how you would change your formula:

If {PickList.OrderMaterialAvailable__Date_Posted} = 
  minimum({PickList.OrderMaterialAvailable__Date_Posted}, {<group field>})
then crRed else 0

If you want to create a summary at a group level instead of at the grand total for the report, you need to add the field you're grouping on to the summary formula after the field you're summarizing.

-Dell

former_member967270
Discoverer
0 Kudos

See the attached example. pick-list.png

DellSC
Active Contributor
0 Kudos

Please explain more about what's right or wrong about what's in your image. I can't diagnose anything if I don't know what I'm looking for.

Thanks!

-Dell

former_member967270
Discoverer
0 Kudos

With my original formula it is looking at the whole printout and marking the oldest date instead of just looking at the section/group. If i have a section/group that isn't the oldest like the image attached, it doesn't change the text to red.

DellSC
Active Contributor
0 Kudos

Assuming part numbers can have multiple locations/posting dates and that you want the oldest date posted per part number, the formula stub I provided above is what you're looking for. You'll have to tweak it to provide your specific field names.

In order for this to work, you cannot just sort by order number and then by part number or part description. You need two groups - the first on order number and the second on part number. Then order descending by Posted Date.

1. Suppress the order number group header unless you need to provide order information before the part information.

2. Put the part information in the part number group header.

3. Put the part location/posted date information in the details section.

4. Format the fields in the detail section to be red using something like the following formula - tweak to use your part number field name.

If {PickList.OrderMaterialAvailable__Date_Posted} = 
  minimum({PickList.OrderMaterialAvailable__Date_Posted}, {Picklist.PartNumber})
then crRed else crBlack

-Dell

former_member967270
Discoverer
0 Kudos

Sorry for taking so long to get back with you. I was sick and then out for the holiday.

I used the formula below matching it to a field in the database but I'm now getting a message that says "There must be a group that matches this field. I highlighted the text that it highlighted when it gave the error.

DellSC
Active Contributor
0 Kudos

This is telling you that you have to have a group on {PickList.Order_Material__Part_Number} in order to do a minimum at that level.

-Dell