TextList Object List by date

I am having a difficult time with a TextList object in PanX. I am converting a Pan6 file and I want to choose from records with a processing date greater than a specified time from today(). For example the list should contain all records within 3 months, 6 months, 1 year, etc before the current date. I have two lists on the page one selects the interval and creates a variable that specifies the date after which I want records to display. The variable is created and is correct. However, the larger Object that shows patient records is not displaying the records I want. The selection formula is shown here:
?(«ProcDate»≥ vDateRange,exportcell(«Name»)+tab()+exportcell(«MR»)+tab()+exportcell(«ProcDate»)+tab()+exportcell(«ProductSource»)+tab()+exportcell(«Procedure»)+tab()+exportcell(«RecordNo»),"")

«ProcDate» is a date field and displays on the list when all records are selected The variable vDateRange is a number that represents the date I want and can be converted to a date using datepattern. However, no records are selected.

I have tried a number of permutations such as date(vDateRange) or even Datepattern(vDateRange,“MM/DD/YY”) and nothing works. I can write a short procedure that works fine “select «ProcDate» => vDateRange” to physically select the records in the data sheet, just not in the TextListObject.

Help would be greatly appreciated.

Text List objects are well documented in the Panorama X Help.

I think you might be interested in the parts Text List Database Integration
and Text List Searching.

Carolyn,

?(test, true, false) is not a selection of records. It just compares some relationship, then returns what you have for true or what you have for false depending upon the result of the comparison. In your example, it is only looking at the current record and might return just the data for that if «ProcDate»≥ vDateRange was true for the current record.

The terms After and Before with respect to dates can be surprisingly ambiguous when it intends “earlier than” or “in the future” because “after” can be behind - Sue is after Fred in line. Or it can mean in the future, “Sue will be served after Fred.” if you look at a linear time line. the dates after - to the left - of a specific date are earlier and the dates before - to the right - a specific date are in the future. English is a fun language.

You said your criteria, «ProcDate»≥ vDateRange, was selecting what you wanted in the data sheet, so I’ll assume they are good and move forward from there.

I think what you want is some kind of array build. I looked at the ARRAYSELECTEDBUILD function but inside the text object it was giving me (handler …) errors.

I would not try to build the array directly inside the text object.
I’d define a fileglobal variable and put the array in that, then display that fileglobal in the text object.

Something like:
FileGlobal vMySelect
Arraybuild vMySelect, chi(13),"", ?(«ProcDate»≥ vDateRange,exportcell(«Name»)+tab()+exportcell(«MR»)+tab()+exportcell(«ProcDate»)+tab()+exportcell(«ProductSource»)+tab()+exportcell(«Procedure»)+tab()+exportcell(«RecordNo»),"")

Then put vMySelect in the text object

I copied your ?(test,true,false) comparison as the formula part of the Arraybuild. I can’t test that. I did create a small database with just three records to test arraybuild, putting the resulting variable in the text object and it displayed them just fine.

If you make your form before you define the file global, you will get a “field or variable not found” warning until you define that variable. I most often define my variables in the .Initialize statement so they are ready to go as soon as the program launches.

Carolyn, looking at your code I think the problem is that you are comparing a date (ProcDate) with a date range. You better split the date range up in two variables (e.g. StartDate, EndDate) and search for dates >= StartDate and <= EndDate and both dates <= ProcDate.

I guess I did not explain the issue very well. I wanted the textlist object “Select Patient Record” to display the data for records where the procedure date (ProcDate) was a specified number of days before today(). The example being 3 months before today(). The variable, which I confusingly named vDateRange was separately created by another text list object called “Select Time Interval” on the same form. The selection options in “Select Time Interval” were listed as Previous Month, Previous 2 months, Previous 3 months, etc, and based on the selection vDateRange was calculated to represent the date that was 3 months prior to today. Specifically if used today vDateRange is calculated as today()-90 with the result being 2458896, or 2/26/20. So indeed I wanted the object “Select Patient Record” to display records where ProcDate≥ vDateRange. Finally I just gave up trying to make that work in the formula for “Select Patient Record” and instead used “info(“visible”)” under Query for the “Select Patient Record” object. Instead I used the procedure section in the “Select Time Interval” object to physically select the desired records in the database. The whole purpose was to reduce the number of records the user needed to view to find the one they wanted without having to go to the datasheet for the file or use the file menus. When the user sees the record they want, they select it and the formula for the “Select Patient Record” object takes the user to another form where the record can be edited. I use this schema fairly often in the databases I code and when the selection criteria for displaying the records in a text list object is text, I have had no problem, only it seems when working with numbers representing dates. I am sure it is something obvious, but my workaround solved the problem for now. My programming skills are not close to those of you posting on this list, and I appreciate your help.