What happens when you want to define the starting position for a label after you run a Query by Form? Here's a solution:
In the July issue, Stan Mathews had a great tip about defining the starting position for labels. I tried it for a couple of different situations, and it worked very well.
However, I decided to incorporate it into an app that one of my customers is using, and ran into a problem. In this case, the customer uses Query by Form a lot (see the article in this issue). And, they will very often run the query without specifying a sort order. Guess what sort order they get? Yep, by record order number. This is fine, if the first record has blank values in the important fields. However, this is usually not the case for an app with existing data. And, I feel it's a lot to ask the customer to make a new record to duplicate the data in the first record, so they can make the first blank (the app has many fields, which would take time to copy to a new record). Sure, I could build a mechanism to duplicate the first record, but I wanted to see if I could come up with a cleaner solution.
Therefore, I set out to add to Stan's idea so it would work with Query by Form. This would involve creating a new query that would run immediately after Query by Form, so I could re-define the sort to include the blank record as the first record in the order. This way, I could be sure that the label position mechanism would still work correctly.
First, I had to include a script in the button that would print the labels, so I could determine if the most recent query was in record number order. Here is the script:
dim global lblpos as n
tbl= table.current()
qbfs = tbl.index_primary_get().order_get()
qbff = tbl.index_primary_get().filter_get()
IF qbfs = "RECNO()"
qbfs = "IF(CUSTID = '','A','B')"
qbff = qbff + ".or.CUSTID=''"
ELSE
goto LBL
END IF
query.description = "Labels"
query.filter = qbff
query.order = qbfs
query.options = ""
tbl.query_create()
LBL:
clblpos = ui_get_text("LABEL POSITION","Enter position for first label","1")
lblpos=val(clblpos)
:label.preview("CUST_LABEL")
I have defined two variables, qbfs and qbff, that tell me what the previous sort and filter were. Then I evaluate the sort to see if it was by record number order. If it is, I then re-define the sort to make sure that the record with the blank field (in this case, CUSTID) is first. I also re-define the filter so the blank record will be included. (If the query is not in record number order, the script goes to the part that prompts for the label position, and then prints the label).
The main idea in this script is that I run a query after the user has run Query by Form (if they don't define a sort). While running a second query might seem inefficient, I have found that the actual process is quite smooth. If the user defines a query that takes a long time to run, the second query will also take some time, but I felt that the trade-off was worth it.
I should point out that this script does not take into account a Query by Form run in reverse record number order. I leave that as an exercise for the reader!
