how to detect a certain date in a dg table column

[CAsba]

Hi,
Here I am again - evidently the LAST of the NEWBIES. Like I've just emerged from the colonial era or something. (See last post).

Anyway, to business...

I want to run code on the condition that my datagrid table column 'reviewDate' contains 'the date' (today's date), and extract data from the row (line).
Any ideas ?

[Klaus]

Hi CAsba,

you need a repeat loop through the datagrids content and check all "reviewDate" columns!

Code: Select all

...
put the dgdata of grp "your dg here" into tData

## A list of all (numeric) keys of the data:
put the keys of tData into tKeys

## What are we looking for:
put the date into tDate

## Will be set to TRUE once we found the date in the datagrid!
put FALSE into tFound
repeat for each line tKey in tKeys

     ## We check this special column here:
      if tData[tKey]["reviewDate"] = tDate then
         put TRUE into tFound
         exit repeat
      end if
end repeat

## NOW check if we had found something and take action if yes:
if tFound = TRUE then
### do your thing...
end if
...

Best

Klaus

[stam]

Rather than loop, I’d use the Filter command, i.e:

Code: Select all

Put the dgData of group “myGrid” into tArray
Filter elements of tArray where each [“reviewDate”] is theDateToSearchFor

tArray will now contain the data grid lines in question, keyed under their index, i.e. tArray[index][the columns of your DG] if that makes sense (set a break point to stop code execution and review tArray if that doesn’t).

In fact a common thing I do with DGs is store the full dataset (when populating) into a custom property of the datagrid. That way I can filter and set the dgData to the found data, and restore the full data set when the filter is empty.

HTH
S.

PS: welcome back from the colonial era

[CAsba]

Thanks Klaus. That's very good.

[Klaus]

Ah, yes, even better!

[stam]

“Each” is the underestimated magic sauce that makes everything easier

[CAsba]

Hi Klaus,
But how do I select (hilite) the line?

[stam]

CAsba,
In general I avoid loops where possible because they can be processor intensive.
From what I can tell, you have broadly 2 choices:

Option 1
Filter the datagrid so it only shows the found lines. Use the Filter method I posted above and set the dgData of the datagrid to the resulting array.
To not lose your data ensure the full data set is stored somewhere - typically I'll store this in a custom property of the datagrid, ie after populating the first time, I:

Code: Select all

set the uAllData of <myGrid> to the dgData of <myGrid>

This creates a custom property uAllData of the datagrid that contains the full data set. When filtering I use the above method to get the found elements of the array and set the dgData of the datagrid to this:

Code: Select all

set the dgData of group <myGrid> to tArray

Since you have record of all data - to restore this, simply

Code: Select all

set the dgData of <myGrid> to the uAllData of <myGrid>

Option 2
You don't filter the datagrid - instead use the Filter method above to get the found indexes. In the resulting array, the data is keyed by the source array element's index. In my example:

Code: Select all

put the keys of tArray into tKeys

tKeys will now contain a return-delimited list of all indexes found - this is not the same as line number.
You can either just set the dgHilitedIndexes of the datagrid to tKeys (keeping in mind you have to change the return delimited list to a comma delimited list), or if you want the line numbers, convert the indexes to line numbers with dgLineOfIndex and then set the dgHilitedLines instead.

Then you would need to scroll down to the found line(s) - use the datagrid's property dgVScroll. But I'll leave that as an exercise to you if you want to do this.



Personally I always go with option 1, much simpler.
Typically I'll have a field acting as a filter and use it's on textChanged handler to run the filter command that includes the above, but equally if the filter field is empty I simply restore the full data set with uAllData. But that's just my preference...

[CAsba]

Hi All,
I came up with another solution - it probably will invoke your disdain in that I probably didn't give enough context to my initial query.
The dg has two columns with dates, reviewdate and lastinvoice. It was necessary (I thought) to specify the column to differentiate them. But an easier way that I am now using is to put the long date into reviewdate, and the abbrev date into Lastinvoice. So now I can just di it by:-

Code: Select all

if the dgtext of grp "Datagrid 1" of cd "custlist" contains the long date then
      #do whatever
   end if

[Klaus]

Hi Klaus,
But how do I select (hilite) the line?

In that case use my loop and extract some more infos:

Code: Select all

...
   put the dgdata of grp "your dg here..." into tData
   
   ## A list of all (numeric) keys of the data:
   put the keys of tData into tKeys
   
   ## What are we looking for:
   put the date into tDate
   
   ## also get the INDEX of the found date
   put EMPTY into tIndex
   
   ## You want all data from that row, right?
   put EMPTY into tCompleteRow
   
   ## Will be set to TRUE once we found the date in the datagrid!
   put FALSE into tFound
   repeat for each line tKey in tKeys
      
      ## We check this special column here:
      if tData[tKey]["tdate"] = tDate then
         put TRUE into tFound

         ## INDEX
         put tKey into tIndex
         put tData[tKey] into tCompleteRow
         exit repeat
      end if
   end repeat
   
   ## NOW check if we had found something and take action if yes:
   if tFound = TRUE then
      ## tKey = the INDEX of the array with the found date
      ## tCompleteRow is an array with the content of the found row
      ## do your thing
   end if
...

Best

Klaus

[Klaus]

OK, but maybe "the long date" is contained in another string in the grid?

[Klaus]

Hi Stam,

Rather than loop, I’d use the Filter command, i.e:

Code: Select all

Put the dgData of group “myGrid” into tArray
Filter elements of tArray where each [“reviewDate”] is theDateToSearchFor

...

Tested and end up with an EMPTY array!?

Code: Select all

...
put the date into theDateToSearchFor
Put the dgData of group 1 into tArray
Filter elements of tArray where each [“tdate”] = theDateToSearchFor
breakpoint
## tArray = EMPTY
...

What am I overlooking?

And yes, in my example the column is named "tdate"!

[stam]

Hi All,
I came up with another solution - it probably will invoke your disdain in that I probably didn't give enough context to my initial query.
The dg has two columns with dates, reviewdate and lastinvoice. It was necessary (I thought) to specify the column to differentiate them. But an easier way that I am now using is to put the long date into reviewdate, and the abbrev date into Lastinvoice. So now I can just di it by:-

Code: Select all

if the dgtext of grp "Datagrid 1" of cd "custlist" contains the long date then
      #do whatever
   end if

If it works, it works - but this is potentially error prone as you don't know for sure that what is found is in the reviewDate column.
If all you want to do is perform an action if a data is present (judging by your code you're just looking for today's long date?), then I would instead suggest:

Code: Select all

put the dgData of group "datagrid 1" into tArray
Filter elements of tArray where each ["reviewDate"] is the long date
If tArray is not empty then
   # do whatever
end if

this is both very fast and ensures the correct column is interrogated. it also means you don't have to format the date as the long date unless you really want that. And no loops

However you are confusing me - first you want to know how to find a line, then how to hilite a line containing the found data and then you say you just want to act if the data is present in the datagrid...

S.

[stam]

What am I overlooking?

And yes, in my example the column is named "tdate"!

Sorry Klaus, 'reviewDate' was the column name given in the OP...

[CAsba]

Hi again,
I was a bit too optimistic; I'm still stuck on getting the line hilited where the date cell lives. How can I get the line ?