list field with conditional formatting?

[wee wullie]

Hi all,
I have an access db that populates a list field containing 8 date columns and some other cols, Can anyone tell me if it would be possible to change either the cell text or cell background colours depending upon whether a certain date has passed?.
I currently use an excel spreadsheet that contains the same data as the above mentioned msaccess db (its a training record that contains expiry dates) i.e. 8 date cols and some others, i have set this up so that if a record expires within 30 days of a certain date the cell turns blue, once it expires it changes to yellow for another 30 days after which it changes to red, i done this using conditional formatting, it uses the system date to make the calcs and it works well.
Is there a way to do this in LC using the list field?

Any help would be welcome.

[Mark]

Hi,

Probably you want something like this:

Code: Select all

set the itemDel to tab
put item x of line y of field z into myDate
convert myDate to seconds
put the seconds into myCurrentDate
if myDate < myCurrentDate then
  set the textColor of line y of field z to blue
end if

Kind regards,

Mark

[wee wullie]

Hi Mark. First of all Please accept my sincere apologies for not responding to your post as i had a hard drive fail on me and have only now got things working again, Sorry.
Thanks for the reply, the code you posted looks as if it should work but is there a way to count the number of items in the table field that contain dates as the table columns are id, first name, last name and 8 date cols and there are roughly 50 rows of data but this can vary.

Thanks Willie

[Mark]

Hi Willie,

In your original question, you say you're using a list field but in your reply you write you're using a table field. Which of the two do you use?

What exactly do you want to check? Whether columns 4 till 11 are filled or whether the text in columnds 4 till 11 is actuall a date?

You can take the text from a field (both list field and table field) and do a repeat loop:

Code: Select all

set the itemDel to tab
repeat for each line myLine in fld x
  repeat with x = 4 to 11
    if item y of myLine is not a date then
      // do something
    else
      // do something else
    end if
  end repeat
end repeat

Since I don't have much info, I'm not sure whether this is what you want but I expect it to be similar to this.

Kind regards,

Mark

[wee wullie]

Hi Mark, i'm sorry, its a Table field that is populated by a MSAccess db that is used for keeping/maintaining training records, the date ranges in the date cols can be anytime from the current date to a maximum of three years in the future when re-training would be required,
I would like to be able to hilite with coloured text any dates that would be expiring within say 30 days with yellow text, already expired in red etc.

if i can help any further please ask

Thanks
willie

[Mark]

Hi Willie,

You should already be able to do this with the script I gave you. You only need to add a few lines.

Code: Select all

put the seconds + 30*3600*24 into theSeconds
put 0 into myCounter
set the itemDel to tab
repeat for each line myLine in fld x
  add 1 to myCounter
  repeat with y = 4 to 11
    if item y of myLine is not a date then
      // do something
    else
      put item y of myLine into myDate
      convert myDate to seconds
      if myDate > theSeconds then
        set the textColor of item y of line myCounter of fld x to red
      end if
    end if
  end repeat
end repeat

This is only an example and there may be more efficient ways to do this.

Kind regards,

Mark

[wee wullie]

Hi Mark, i tried that and it seems to work on some cells but not on others even with the same date but i'm still tweaking it.

cheers
wullie

[bn]

Hi willie,

here is a small stack that colorizes the dates according to what you wrote. It creates random dates and puts it into a table field.

Could be tweaked to work faster but for 50 lines it should be OK.

colorizeDates.livecode.zip

(1.95 KiB) Downloaded 298 times

Kind regards

Bernd

[wee wullie]

Thank you Bernd, that works well, its very fast indeed, however i get an error when using it in my table, probably because my table contains more than just dates, i has ID, First Name, Last Name and 8 date columns and not all date fields contain dates, some contain N/A so i'm sure the problem is with non-dates,
i'll mess with it.

Thank you.

wullie

[bn]

Hi Wullie,

glad it works for you. If you have trouble with non-date entries you could add a line(the commented line):

Code: Select all

 repeat with i = 1 to tSoManyLines
      repeat with j = 1 to tSoManyItems
         put item j of line i of field "MyTable" into tADate

         if tADate is not a date then next repeat -- test if it is a date else go to next item

         convert tADate to seconds

this is a quick and dirty way to solve the problem as long as no other dates beside the ones you want to colorize are among the items.

The more elaborate way would be to adjust the repeat loops to your data.

If the dates start always with item 4 and go to item 12 you could change the repeat loop for the item accordingly.

Kind regards

Bernd

[wee wullie]

Hi Bernd,

i tried adding the line as you said:

Code: Select all

repeat with i = 1 to tSoManyLines
      repeat with j = 1 to tSoManyItems
         put item j of line i of field "MyTable" into tADate

         if tADate is not a date then
 next repeat

         convert tADate to seconds

i dont get an error anymore, but nothing happens, i'm not sure how to adjust the repeat loops, would it be something like:

Code: Select all

repeat with i = 1 to tSoManyLines
      repeat with j = 12 to tSoManyItems
         put item j of line i of field "MyTable" into tADate

         if tADate is not a date then
 next repeat

         convert tADate to seconds

[bn]

Hi Wullie,

I modified the stack. I added a button that only checks items 4 through 8 of each line for dates.
This assumes that you have some data in item 1 to 3 of each line and some more data in items 9 through eleven.

Of course the date format would have to be a format Livecode knows of. But the english short date should be OK.

colorizeDatesII.livecode.zip

(2.85 KiB) Downloaded 282 times

if you still have trouble it would be helpful if you posted a sample line of your data. Exactly how it appears in your table field.

KInd regards

Bernd

[wee wullie]

Bernd, I think i've got it, that stack worked although both buttons yielded the same result, and it changed the valid dates red (the dates on my own table field) rather than the expired dates so i kept that format and added another condition so that i now have:

Green for currently Valid,
yellow for expired in the last 30 days,
127,127,255 for expires in the next 30 days
red for expired for more than 30 days

not sure why i had to change the colours around but it seems to be working and its fast, i also had to add a few more lines of code to make the background color white for items 4, 6, 8 and 10 as i didnt want these date fields colorized so thank you very much and thank you Mark for the excellent expert assistance you have given me (again) ,

Cheers guys

Warm regards
Wullie