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.

Carolyn, Pardon my density but I am going to use the term “earlier” rather than “before”. Just makes it clearer for me. I’m not sure what you understand about dates but a date is just one big number that is the number of days since the birthday of Macintosh or 1/1/1776 - something like that. You only see it as month/day/year or some part of that because of display options. If the date is just a count of days since some starting date, then the duration between two dates is just the most recent date minus the earlier date.

If DateRange represents the desired age (30, 60, 90 days), desired records would be:
Today() - ProcDate <= 30 for last 30 days
Today() - ProcDate > 30 AND ProcDate <= 60 for 60 days
Today() - ProcDate > 60 AND <= 90 for 90 days.

If DateRange is your desired interval; i.e. number of days old from Today(), (30, 60, 90)

Then your selection could be a Forumla Select like
(Today() - ProcDate) > (DateRange - 30) AND (Today() - ProcDate) <= DateRange

Let’s see. For 30 the interval would be > 0 and <=30, everything from yesterday to 30 days earlier.
For 60 the interval would be > 30 AND <= 60, everything older than a month and less than/equal to 2 months.
For 90 the interval would be > 60 AND <= 90, everything older than two months and less than/equal to 3 months

*** big time saver coming ***
However you make your selection, Remember that you can have both the data sheet and a form open at the same time. You can resize the windows so the data sheet and your edit form are side-by-side. All you need to do is click on one of the (selected) records in the data sheet and the forum will display that record. Just click on the form and start editing.

Maybe you have it already set up like that - but it sounded like you might be switching back and forth from Data Sheet to Form, opening and closing windows.

In the old days, it was not uncommon to present a View-As-List form that showed customer/business names (records selected by some process) in a window along side a form that showed the rest of the details of that record for editing. Just click on a View-as-List record and the rest of the fields populate the “Edit” form in the adjacent window.

I’m not sure about View-as-List actions in PanX but I’m sure you could do it just by resizing the data sheet to show client/business name (or any other identifier/Field) and put your edit form next to it.