Changing colour of field based on a range of data

[rkass068]

Hello I am having some trouble with conditional formatting. 

 

In my database i have a table with the fields entry type, start date and end date

 

These fields are in a portal on the main page and it displays the info perfectly and the way I want it. However, I want to change the colour of some fields based on the Start and End date. For example, If I set the field (entry type) as lab with the date the lab work was done, and another entry (record) with the entry field set as chemotherapy with a start date and end date, I want the lab row to change a different colour if its date falls in between the start date and end date of the chemotherapy. 

 

Is this possible? Please let me know if more clarification is needed. I tried using an sql calculation to list all of the chemo dates and tried to conditional format based on the query list but I was out of luck.

 

Thanks 

[comment]

I want the lab row to change a different colour if its date falls in between the start date and end date of the chemotherapy.

 

Are these two records in the same (portal) table? If yes, where exactly is the date of the lab? You say the table has "fields entry type, start date and end date".

[rkass068]

Are these two records in the same (portal) table? If yes, where exactly is the date of the lab? You say the table has "fields entry type, start date and end date".

 

Sorry, yes my question was a little vague. The start date and end date for both entries (Lab and Chemo) are in the table called "entries" This is kind of a hospital encounters table. Data pertaining to each encounter is in their own separate tables, which has nothing to do with the conditional formatting. All that I need to do is change the colour of the fields in the portal that follow between the start date and end date of any chemo records. I have a field called "entry type" which determines the purpose of the entry/encounter. 

[eos]

See if the attached file helps you.

ChemoCF_eos.fmp12.zip

[rkass068]

See if the attached file helps you.

 

You sir are one smart man! Thank you very much

[rkass068]

See if the attached file helps you.

Sorry, I am having problems now with your solution. It seemed to work before but now I see a fault in it. I will post the file and you can see that the lab changed colour but it is not between any chemo start and end dates.

ChemoCF_eos.fmp12.zip

[eos]

but it is not between any chemo start and end dates.

 

So what you meant was "a lab entry whose date is in the range of any single chemo entry".

 

Try the modified file. The relationship to find lab matches is defined to be inclusive, i.e. uses ≤ and ≥ operators. Change that to < and > if you want it to work non-inclusive.

ChemoCF_eos v2.fmp12.zip

[rkass068]

So what you meant was "a lab entry whose date is in the range of any single chemo entry".

 

Try the modified file. The relationship to find lab matches is defined to be inclusive, i.e. uses ≤ and ≥ operators. Change that to < and > if you want it to work non-inclusive.

Yes, sorry should of clarified that!

 

Thank you so much!

[rkass068]

So what you meant was "a lab entry whose date is in the range of any single chemo entry".

 

Try the modified file. The relationship to find lab matches is defined to be inclusive, i.e. uses ≤ and ≥ operators. Change that to < and > if you want it to work non-inclusive.

Hello, I am really sorry to bump this topic but I want to ask another question about this. Is it possible to have the colour change all the records added in the lab table until the stop date is put it. For example for chemo if i put todays date, every subsequent lab would turn colours until a stop date is put in. Right now it will only colour if both the start and stop date are filled. Thank you!