Okay so let me describe the form and it's purpose so this all makes sense. Basically it is a monthly inventory list that personnel enter the expiration date of numerous items with the intention of making it easy for our supply person to order the stuff that is going out. Mind you that there could be 100+ items to do this for on a document. The company is 501(c)(3) and can't afford to buy some expensive inventory program to do this stuff, and we are in the medical profession so this is beyond "out of our wheelhouse" so to speak. I would appreciate any thoughts that you may have.
So at the top if have "Today's Date" which is "DateField1" and the personnel's name.
I have it structured in a table that shows the name of the item "Cell1", the amount that should be there "Cell2", the number they have on hand "Cell3", the expiration date of that item "DateField2" and the last column is a numeric field which is "NumericField2" that is calculated to show the number of days from today to the input expiration date using
form1.sf1.Table1[0].Row2.DateField2::exit - (FormCalc, client)
var datePattern = "MM/DD/YYYY"
var startDate = Date2Num(DateField1.formattedValue, datePattern)
var endDate = Date2Num(DateField2.formattedValue, datePattern)
var numOfDays = endDate - startDate
NumericField2 = numOfDays
the formula does as intended and yields the total number of days between "DateField1" and "DateField2" without issues. What i would like it to do is immediately after performing that calculation and getting the number of days it changes the fill color of that field based on the number of days as follows
Less than 30 days turns the field red
Greater than 30 days and less than 60 days it turns orange
Greater than 60 days and less than 90 days it turns yellow
Greater than 90 days it turns green
Now all of this i can complete with the action builder on NumericField2 but that requires the user to "exit" that field, and being that it is a calculated field they would have no reason to actually click on the field and leave it thus completely nullifying that. I have absolutely no clue how to force the color change to happen immediately after calculating without the need to have an exit function.
I even went so far as to try and implant a radio button or normal button to use at the end of the document where it would check all of the numeric fields and change the fill colors then but that has been unsuccessful thus far. I also considered rather than having the fields change colors, just have a list be generated on a seperate page that would pull the item's name from "Cell 1" and list them under "Items less than 30 days out" Items between 30 and 60 days out", "items 60 to 90 days out" but I can't even figure out how that would work.
