Querying the results of a Text List query


#1

I’m building a Text List in Pan X to take the place of a View-as-List form I made in Pan 6. It is working well, but I’ve encountered a place where the view-as-list form has access to data that is not obvious how to access in the Text List.

This form usually has no more than about 40 lines in it due to the type of data it is retrieving. In Pan 6 I did a Select to get the records I wanted, then a couple of Total commands to total 2 numeric fields, which are then displayed on the form using a Summary tile. If there is a way within a Text List to derive the same information, that will take care of my problem. Waiting …

In the meantime, I am considering another solution, which is also problematic. I could process my Text List results a line at a time, and perform the summations of these fields in code. If I click any of the lines, info(“matrixrow”) tells me which line I am on, and tabarray( will give me access to the data in certain columns of that line. That’s all I need, but I am not clicking anything, I want to evaluate the entire matrix of the Text List one row at a a time in a procedure. I am not at all clear on how I can retrieve one row’s tabarray at a time, and process data according to particular elements in that array, without having to do any clicking. I have been all over the Help entries for Text List and Matrix programming, but they all seem to be oriented toward interacting with the matrix rather than processing the contents. Ideas?


#2

It sounds like you might want to do an arraybuild with a query formula (new in Panorama X) to build a variable with the data you want.

In your final paragraph you talk about “processing the contents” of a text list, but there really isn’t such a thing. You can modify how the contents of a text list are generated, but there is no processing after that.


#3

I think I have a form with a text list that does what you are trying to do. See below. The text list is not connected directly to the data with Database Navigator checked; it is not checked. The selection of rows to display is controlled by putting the a query into the Query box of the Text List Options. My formula is this:

PayNo beginswith fgspmonth and DT="PT" and
((exportline() contains «_liveSearch») or («_liveSearch» = ""))

With the variable fgspmonth, I can cause the text list to display data for a particular month. The database field PayNo contains a unique payment number, but the first four digits contain the year and month (YYMM). (I am not worried about the Y3k problem.)
At the top of the text list is a text display object with a forumla that replicates the text query, and calculates the total for a column displayed in the text list:

"Total:             "+pattern(ignore(aggregate("Paid","+",
{PayNo beginswith fgspmonth and 
DT="P"}),fgspmonth),"#,.##")

The only negative I have found that generating this form and loading the data is slow. With my database of 21,000 records it took about 35 seconds to display the first month, then 17 seconds to switch to a different month. I hope some day to do this faster.


#4

I guess I imagined that a Text List actually represents a two-dimensional array, where one dimension is the tabarray that constitutes a line (row) and the other dimension is a row number. If such an array existed, then extracting any info you needed from the Text List output would be simple. This is what I mean by “processing the contents.” But I can use the arraybuild technique, rather redundantly, to extract the numbers that I need to total. Am I correct in assuming there is no way to apply a “total” operation to certain columns in a Text List?

By the way, during this process I discovered a sure-fire way to crash Pan X. Part of the reason I am using a Text list approach is that I need to sort the list on a value I get from a lookup(, not a field in the main database, which I don’t believe can be done in a View-as-List form. So after I had my list constructed the way I wanted it, I went to the Text List Options pane and selected Sort Up in the Sorting control. Crash! Did this 3 times before I realized that the Database Navigator box was checked. Not surprised that these settings are in conflict (I hadn’t been paying any attention to the DN setting up to this point, and apparently it defaults to ON) but it ought to gracefully inform the user of this conflict rather than crash.


#5

Tom, that is pretty much what I realized I need to do, basically repeat the query to get my data in a different form that I can calculate with (see reply to Jim). Thanks for your explanation.

By the way, there is no reason to worry about the “Y3K” problem because none of us will be around, but the next round of the “Y2K” issue will be in 83 years, much sooner than that, i.e., the Y2.1K problem. It is the change in century that exposes the limitations of using only 2 digits for a year, not the millenium. We had both happen at once in 2000 which confused the issue. I was well involved in doing Y2K testing in 1999, and predicted that by 2100 it will be revealed that some programmers got lazy with data storage again and will face the same issue all over.


#6

I doubt that many of us will be around in 83 years either Scott - maybe the odd child prodigy.

michael


#7

True enough, but 2100 is close enough that our children and grandchildren may get to experience the consequences of it and it could be an actual concern. Thinking this could not come around for another 1000 years so who cares is dangerous thinking.


#8

I am getting nowhere trying to set up an arraybuild to retrieve the data I want here. No matter how many times I read the documentation, I can’t discern the requirements of the 4th and 5th parameters, i.e., the “formula” and the “query”. The description does not make it clear to me what should be in those parameters. I can get it to run without error, but it isn’t returning anything, let alone what I’m after. Maybe someone can straighten me out.

Here is the description of how the statement is to be formed:

arraybuild ARRAY, SEPARATOR, DATABASE, FORMULA, QUERY

The first 3 are easy: ARRAY is a local variable AdjArray I have declared, SEPARATOR is “;”, DATABASE is current, so “”.

I want to retrieve the contents of field «Adjusted Total» (numeric) in the database, and the query I have set up for the Text List is exactly appropriate for this data. This query is contained in a local variable MyQuery, whose contents are thus:

"monthvalue(«PayDate») = “+val(mymo)+ " and yearvalue(«PayDate») = “+val(myyr)+” AND «Invoice No.» > 0 and State = ‘WA’”

variable mymo and myyr are set up earlier in the procedure. In the Text List it gets me exactly what I want. So I have constructed my statement like this:

ArrayBuild AdjArray,";","","Adjusted Total",MyQuery

Whether I put quotes or chevrons around Adjusted Total it makes no difference. I have put the query in literally instead of as a variable, no difference.

I just don’t understand, and maybe because it’s Friday approaching a beautiful weekend here. Help is appreciated.


#9

If I’m reading this correctly, you want the statement to be

ArrayBuild AdjArray,";","",«Adjusted Total»,monthvalue(«PayDate») = val(mymo) and yearvalue(«PayDate») = val(myyr) AND «Invoice No.» > 0 and State = 'WA'

Your query formula would have generated a string of text, rather than a true or false (-1 or 0) value.


#10

Dave’s answer is correct. I think you could also do this with the arraybuild( function, like this:

AdjArray=arraybuild(";","","«Adjusted Total»",MyQuery)

#11

Man, I was so close! I had tried the arraybuild( function approach with similar unhappy results, but the trick of putting quotes around the chevron parts of the variable eluded me. Tried it with just quotes and just chevrons without luck. Using it the way Jim describes did exactly what I need.

I also tried basically what David described, but put the literal query in quotes which is what got me a big string of text instead of desired data, as described.

I would not have reached these solutions from my understanding of the documentation as written. Thank you both.


#12

When I saw how slow my forms were loading, I went back and created them differently. Instead of putting the search criteria inside the text list in the Query field, I just created an array with the data I wanted and displayed that. The new approach was at least 30 times faster. I am not surprised that it is faster, but I am surprised by how much faster. I have lost some functionality: I don’t presently have a search box. One could do a search but it does not seem feasible on my database to redisplay the results for every keystroke.