In my previous database program (Bento, now defunct) it was possible to do an Advanced Search and then save it, so that the results could be seen at any time, like a subset of the parent database. Those results could also be sorted differently from the parent database.
So my Books database, for instance, is sorted first by author, then by title. But in Bento I have many saved searches for series of books by one particular author (e.g. Agatha Christie’s Poirot mysteries), which I can then separately sort by the number in the series—so if I ever want to, I can reread those books in their correct order.
Thanks, I see there are a lot of possibilities there that I wasn’t aware of. It does say, though, that sorting always applies to the whole database, not the current selection; so it doesn’t look like I could get just a subset (one author’s books) sorted separately from the whole.
It also mentions the possibility of dividing the database into groups: but again if that applies to the whole DB—so that every record must belong to a group—it would also be a problem, because the “saved searches” I’m looking for would only apply to books by certain authors, and only to some of that author’s books.
I realise Sort Up Within could give me a sort order over the whole database in which each author’s books are sorted by series, and then by number within the series; but (a), what then happens to the many books that are not part of a series? Will they just be in random order? And (b), connected with that, I would still want the book title as a secondary sort order for the whole database—but that’s incompatible with sorting by series.
So unless I’m missing something, it looks as though I won’t be able to replicate my “saved searches” in Panorama. But I’m hoping you guys will prove me wrong, as Panorama always seems to find a way of doing what at first sight looks impossible!
There is almost nothing impossible in Panorama X. Read the Help about Sorting.
You can combine criteria; you can sort within previous sorts at your will, and you can save this setup as a Favorite Search.
Or you can open a procedure window, click record, then do what you want to do in the data sheet: You can record your steps and save them as a procedure. Later on you can invoke this procedure from the Action menu or with a Hotkey.
Hi Kurt, thanks for your comments. I’ve read the Help on sorting very carefully, both “106: Sorting the Database” and the “Sorting Data” topic. But I can’t get around the fact that Panorama always sorts the entire database. This, and the incompatibility of sorting by Title and sorting by Series, makes it impossible to get what I’m looking for without losing either the Title sorting or the Series sorting. So either each author will have his/her non-series books first, sorted by Title, followed by a jumbled set of titles sorted by their series and Number_in_Series; or the whole database is sorted by Series and Number_in_Series, which is a total mess.
The incompatibility of sorting both by Title and by Series is a problem of life and the nature of books; the only problem with Panorama is that it does not allow for independent sorting of a selection of records from the database. If it did that, all my problems would be solved and I could duplicate what I had in Bento. (Dare I request that this be added as a feature request for future versions?)
It seems I therefore have to decide whether to make do with the first outcome above, where some of the books are sorted by Title and others by Series; or to set up a whole lot of separate Author databases, with each sorted by Series and Number_in_Series, not by Title.
Have you considered using a text list object? Most likely, this would be on a new form, but it can be sorted separately from the database. You can select whatever you want to fill it, and it responds well to programming, so filling it with the books of one of a select set of authors should be relatively straightforward. One form could have books sorted by number (and the selections could be limited to those books for which it made sense), and another sorted by author. There is also no reason not to have more than one text list on one form, if you have space.
I’m sorry that I didn’t have time to try this out, but I have grown rather fond of this feature, due to its flexibility and speed. It’s worth a look.
Do you have your Panorama preferences set to allow analysis of selected records? If this is checked you can sort only the currently selected set of records.
Wow! So you can sort a subset of the database? That sounds great!
Except… I tried it, and that didn’t seem to happen. I enabled “Allow analysis of selected records” in Preferences, and then created a selection consisting of one author’s series (several of them) and sorted the selection by Series and Number_in_Series. That worked fine with the selected records; but when I reverted to the full database, it had sorted all the records that way—so non-series books all appeared at the top, followed by _all_the series in the database in alphabetic order, regardless of authors!
I also tried it using Sort Up Within instead of the normal Sort function; and that produced a better result, with the main Author sort-order still intact and the series grouped and sorted within it; but again, the whole database was affected, not just the selection.
So I don’t know if I’m missing something here… Did I misunderstand your original suggestion?
That is the way it works. Every sort effects the entire database, both visible and invisible records. The preference selection just affects whether the invisible records stay invisible while you analyze.
OK, so that unfortunately means this isn’t the solution to the problem. It would really be good if independent sorting of a selection could become possible one day… Or the ability to save searches in the same way that one can save sorts.
I think you can do this by using arrays and sorting them, but I am not certain exactly what you want to do.
When I do this sort of thing, I sort in reverse order, which I think would be Series, Title, Author, in this case. You can Sort Within in the opposite order.
Thanks for your response. In a nutshell what I want to do is to be able to quickly call up a subset of my data (books by a single author) which will be sorted differently than the main database (by series and number-in-series).
Can arrays be sorted independently from the main database?
To elaborate on what Kurt is suggesting, here are the bare bones of a procedure that can contain a series of various selections, sortings and groupings that can be selected from a popup menu and executed. You can then revert back to the starting condition by choosing “Undo Favorite Selection” from the Edit menu. Each saved configuration can be either entered as code or recorded as code and placed at the bottom of the procedure under its own name ending with a : along with a following return statement at the end of the subroute as show in the examples below. There are no limits to the number of custom selections you can add in this way.
local theSubroutines, theChoice
theSubroutines=getproceduretext("", "Favorite Selections")
arrayfilter theSubroutines,theSubroutines,¶,?(import() endswith ":",import()[1,-2],"")
theSubroutines=arraystrip(theSubroutines,¶)
theChoice=""
popupatmouse theSubroutines, "",theChoice
startdatabasechange "ALLRECORDS", "Favorite Selection"
shortcall (theChoice)
stop
OneSort:
field "A"
SortDown
select «C» > """"""
return
TwoSelection:
field "C"
SortUp
select «A» > """"""
return
Here is a simple example of how this scheme works in action:
Yes. Arrays are like miniature databases. They can be entries in a record’s field, or built up on the fly. I guess in this case, you would want to construct an array of each author’s titles and series and sort that array, I guess you would want to do it by series.
Gary, I appreciate your taking the time to respond in such detail, and I’m really sorry to have to confess that it goes way above my head! I’m still a long way from understanding the language used in procedures; and I’m afraid I also failed to grasp the abstract example in the video.
I don’t want to take up too much more of your time; but if you could describe briefly in more concrete terms what the output of such a procedure would look like (a list of titles by a selected author sorted by series? a re-sorting of the whole database?), it might help me get a better idea of what you’re proposing.
Thanks for bearing with me on this rather steep learning curve!
OK, I guess I’m struggling to understand exactly what a Panorama array is, and where it would be stored/entered. The normal meaning of “array” is a two-dimensional table with categories along the horizontal and vertical axes, and values entered in the table for what happens when those categories intersect. But the example given in the Help topic “Text Arrays” looks like just one axis of such a table: “white/red/orange/yellow/green/blue/black”. What would that correspond to in my Books database? A list of titles for one author? Where would the Series and Number-in-Series info be entered?
And my second question is, where would I create/store such an array in my database? You say it could be “entries in a record’s field”—do you mean the array would be stored within a single record? How does that work when it’s referring to multiple records? What does your alternative mean, that it could be “built up on the fly”?
I guess all this shows that I’m not a statistician or data analyst, and have no idea how arrays are used in those contexts. I’m just trying to find a way of extracting the relevant information from my database and sorting it differently from the parent database. But I don’t want to involve you or others in a lot of tedious explanation, so maybe I shouldn’t venture too far out of my depth here. I’ll understand if you feel this forum is not the place to try and get me caught up.
The first section of my example procedure is just the mechanics used to build the popup menu with a list of available subroutes listed below in the same procedure. Once a choice is made from that popup menu it executes that particular subroute as listed below in the second section of the procedure. There is a final line there to start the database change so you can use the Undo to revert back to the state of things before the subroute selection was executed.
The simplest way to generate your own subroute would be to create a new procedure name something like Special Selections and add the top part of my sample code above the two example subroutes. Add a couple of returns and then enter a name for your first custom subroute selection.
MyAuthors:
Use whatever name best describes the nature of you custom subroute followed by a “:”. Then simply click on the recorder tool in the procedure tool bar:
Now go to the data sheet and do all the things you need to do to create your custom selection, sorting and grouping. When finished go back to the procedure window and click the record icon once more. The code for all the actions you have just taken in the datasheet should now be entered at the bottom of the procedure. Simply enter a “return” line at the end of that code and you now have everything you need to replicate that action in the future. Add as many more uniquely named subroutes as needed at any time. Just remember to conform to the proper syntax as shown in my original example. I tried to keep my example subroutes as simple as possible but yours might be very involved as generated from the recorder.
I certainly don’t mind spending some time helping a new user to get up and running as long as I’m not asked to write their entire program.
Thanks, Gary, that gives me a better idea of what you’re suggesting. So basically all my ‘special selections’ would be listed as subroutines in the popup menu; and your proposal is that I go ahead and sort my selections by Series and Numbers, even though I know that will mess up the rest of the database—because I’ll be able to Undo it afterwards. Is that a reasonable summary?
That would produce the outcome I’m looking for, but I can see two possible problems with it: First, if try to reproduce all the ‘special selections’ I’ve already created in Bento, there would probably be too many for a popup menu—I have 77 of them, and I still create new ones from time to time. Quite a few of the older ones could now be left out, but it would still be a pretty lengthy popup list!
Secondly, these lists would be very temporary because of the need to Undo them every time to restore the database to its default sort order. I could not leave such a selection in place when closing the app; and I could not make any permanent changes while such a selection was in place, since they’d all have to be undone—if I’m understanding your suggestion correctly. I might also find it difficult to jump from one ‘special selection’ to another and back again to make comparisons, etc., because each selection would have to be created afresh every time.
These problems may not be deal-breakers (for instance I’ve already recorded a procedure to restore the default sort order); I’m just thinking aloud about how it would work out in practice. Thanks for suggesting this way forward.
Panorama X has unlimited Undo’s so you will be able to back step from one to the next and then forward again using the Redo in the Edit menu.
You could have more than one of these procedures with alternate names for different categories all available from the Action menu.
If you save the file while it was in another configuration it would then open in that configuration the next time.
The other possibility is that one of your subroutes is to set to the default configuration. Then you need not use the Undo to go to the default and your changes made in the other subroute would be maintained. You would just have to be mindful of that requirement.
As I mentioned above, you could do the Undo/Redo dance to jump back and forth between configurations.
