Split text list variable


#1

I have a database for my cattle property. I want to search the database for cattle in a certain paddock and property. Previously I used the FindSelect statement in my programme (old statement tolerated in Panorama 6, which had the advantage of specifying the field to be searched in the data).

In the absence of an equivalent option (as far as I am aware), I have set up a TextListObject, with 2 columns and no duplicates, to show the available combinations of Paddock and Property. I then want to select the data matching my selection. However, the Text List allocates my selection to a variable, and I need to search 2 fields to get a match. Any suggestions on how to proceed?

(I’ve tried to create a separate field with the merged Paddock & Property fields, but it keeps bombing, so that’s not an option at the moment)

Thanks


#2

I should mention that in the past I did the search in 2 steps - first the property, then the paddock. I’ve toyed with setting up individual text lists and doing it separately, a) it looks clumsy, and b) unless I put the text lists on different pages I may select a paddock and property that don’t co-incide.


#3

As you say, Panorama X doesn’t support the FindSelect statement, which opened the “classic” style of Find/Select dialog. It does, however, support the FindSelectDialog statement, which uses the modern search dialog. So I think you could change FindSelect to FindSelectDialog and you’d be all set. (You can use the Preferences dialog to set the default search to the current field instead of all fields.)


The selection result in the variable will contain a tab between the two columns. So your selection code would look something like this:

select Paddock=tabarray(listVariable,1) and Property=tabarray(listVariable,2)

Of course I may have the 1 and 2 reversed, it depends on how you have set up your list.

You could also do the split in advance:

local chosenPaddock,chosenProperty
chosenPaddock = tabarray(listVariable,1)
chosenProperty = tabarray(listVariable,2)
select Paddock=chosenPaddock and Property=chosenProperty

#4

Thankyou thankyou thankyou!. I’d tried the ,1 and ,2 bit, but missed the word tabarray, so it hadn’t worked.
Could you just clarify :

(You can use the Preferences dialog to set the default search to the current field instead of all fields.)

Is it possible, from within the programming, to set the default search field? I don’t wont the generic search dialog because I want to guide the person using the programme to what they should be searching…

Thanks again


#5

PS. Also congratulations.
Panorama X is starting to shape up to something I can imagine working. The new documentation is a FANTASTIC ADVANCE, and the programme is a lot more stable.


#6

It’s not really designed to be programmed, but you can do it.

setpreferencevalues "PXDefaultSearch","Current field"
findselectdialog

You can use this formula to find out the current setting:

getpreferencevalue("PXDefaultSearch")

To set this manually, open PanoramaX>Preferences and go to the General tab.

For maximum guidance, I would use the Text List as you were working on. It’s really quite easy once you get the hang of it, and that way the user really only has the options you give them. (By the way, the Find/Select dialog itself is created using a Text List for the preview.)


#7

And another question about using the variable. If I have the text list set to allow multiple selections, the variable seems to save with a carriage return between selections. If I use tabarray ,2, it gives the property of the first selection, and the paddock of the second. I have tried playing with the separator option in the text list setup, but with no obvious effect at this stage.


#8

To set this manually, open PanoramaX>Preferences and go to the General tab.

Oh my… So easy!. That’s what I haven’t liked about the new search dialog all these years.


#9

I hate to tell you this, but Panorama 6 has had that preference setting all along (7 years).

If you want to allow multiple selections, that will require a huge increase in the complexity of the code. You’ll need to use a loop, processing one line at a time. If I was doing this, I would probably build up the selection formula in a variable and then use the execute statement to actually run the selection. The looparray statement can help with this.


#10

So, I’m still working with this idea. I’ve been trying to figure out how to make the separator between selections a tab, so that the tabarray,number, you mentioned above will work. After much looking I went back to Pan 6 documentation and found the ¬ to indicate a tab separator. However, when I put this in the text list options separator area, it uses that symbol rather than a tab. Any advice??

The alternative idea I’ve had as I’ve been looking through all the documentation, is using a programme to search the field in the database, and make a group of checkboxes using the words retrieved from the field - set up so there can be multiple options (like your pizza topping demonstration) and then using the looparray( to execute the appropriate search. I might be able to figure out how to set this up eventually, but if anyone is able to quickly throw together some ideas to put me in the right direction, I’d really appreciate it.

Thanks


#11

When you choose a row in a two column text list, Panorama puts the content of that entire row into a variable. A tab separates the part that came from the first column (the property) from the part that came from the second (the paddock.) Jim was using tabarray( to extract each separate part, so that he could compare property to property, and paddock to paddock. If you made the tab the row separator, as well as the column separator, you would just be making it a little more difficult for your procedure to tell a property from a paddock.


#12

Thanks, but the two columns aren’t the problem. That works well with what Jim suggested for a single line, double column situation. The problem is if I select multiple rows. The default separator seems to be a comma, so the tabarray (number) request gives 1: “Paddock1” , 2: "Property1, Paddock2”, 3: “Property2, Paddock3”. Therefore I’m trying to change the comma to a tab.


#13

Actually, the default separator is a carriage return. It’s only a comma if you specify it here.

When you are choosing multiple rows, and there is more than one column, you need nested array( functions to isolate a value from a specified row and column. If the variable containing your choices is named theChoices, and you want to extract the value in column one of the third choice, you could use this formula for carriage return separator.

array(array(theChoices,3,cr()),1,tab())

The inner array( function first extracts the third line from your choices, which contains two values separated by a tab. Then the outer array( function extracts the first of those two values. To get the value from column two of that same choice, the formula could be

array(array(theChoices,3,cr()),2,tab())

The tabarray( function is just an array( function that doesn’t need to be told the separator is a tab. It just assumes that to be the case, so those two formulas could also have been written

tabarray(array(theChoices,3,cr()),1)

and

tabarray(array(theChoices,3,cr()),2)

If your main separator is a comma, rather than a carriage return, those formulas can be written like this.

tabarray(array(theChoices,3,","),1)

and

tabarray(array(theChoices,3,","),2)

#14

Thanks. That’s what I needed to know!!!


#15

If you are using LoopArray to step through the choices, as Jim recommended, it will actually take care of isolating each row for you, so that all you need is tabarray( to separate property from paddock. It could go something like this.

Local thisChoice
thisChoice = array(theChoices,1,cr())
NoShow
Select Property = tabarray(thisChoice,1) and Paddock = tabarray(thisChoice,2)
LoopArray theChoices,cr(),thisChoice
    SelectAdditional Property = tabarray(thisChoice,1) and Paddock = tabarray(thisChoice,2)
EndLoop
ShowPage
EndNoShow

The LoopArray will step through the array of choices one by one. Each turn through the loop will have a different choice in the thisChoice variable. The SelectAdditional statement will then add the record, or records, that match that choice to your selection. The first SelectAdditional is actually redundant. The records it would be adding are the ones selected by the initial Select statement.

Jim said that if he were doing it, he would use the LoopArray statement to build A Select statement, and then execute it with an Execute statement. Thats a bit more advanced, but it would be faster.

Local theCode, thisChoice
theCode = ""
LoopArray theChoices, cr(), thisChoice
    theCode = sandwich("", theCode," or ") + {Property = "}+tabarray(thisChoice,1)+{" and Paddock = "}+tabarray(thisChoice,2)+{"}
EndLoop
theCode = "Select " + theCode
Execute theCode

This assumes that the Property and Paddock fields are both text fields. If the field were numeric, I wouldn’t put quotes around the value.


#16

Thankyou so much. My programming knowledge comes entirely from Panorama documentation and these discussion panels, and it often takes hours of trawling through documentation and trial and error to get something like this to fit my purpose.

Much appreciated.


#17

Thankyou again. I’ve now had a chance to play with these suggestions, and the use of the code works perfectly to deliver the right text. However the search doesn’t work as planned. I assume this is the combination of ands and ors in the same search…("Select Paddock = “a” and Property = “b” or Paddock = “c” and Property = “d”) Is there some way to group them so it DOES work?, or do I need to revert to the way I would normally have done it, with a Select search, followed by Selectwithin?


#18

It looks like there is an issue with operator precedence. The AND operator is supposed to take precedence over the OR operator so that

Select Property = "a" and Paddock = "b" or Property = "c" and Paddock = "d"

should produce the same results as

Select (Property = "a" and Paddock = "b") or (Property = "c" and Paddock = "d")

but the first version is only selecting those where Property = “c” and Paddock = “d”, while the second version works correctly. The solution therefore is to add the parentheses to force the correct order of operation.

Local theCode, thisChoice
theCode = ""
LoopArray theChoices, cr(), thisChoice
    theCode = sandwich("", theCode," or ") + {(Property = "}+tabarray(thisChoice,1)+{" and Paddock = "}+tabarray(thisChoice,2)+{")}
EndLoop
theCode = "Select " + theCode
Execute theCode

#19

Hooray! That works perfectly - and will enable me to rewrite quite a few older procedures to work much more efficiently.

Now my remaining issue for this particular task is the format. When I set up a text list which allows multiple values, it is only accepting sequential values in the list. When I use a segmented button, I can select any of the options, but the horizontal orientation has me needing to use a really small font, and falling off the side of the page anyway. Is there

  1. a way to allow non adjacent selection in the text list?
  2. a way to orient the sequential button options vertically instead of horizontally?
  3. another button or format that will suit this purpose better?

#20

Command-click your selections to make non-adjacent selections.