Select data according to a list


#1

I’m hoping Pan X has a slick way to do this. Maybe Pan 6 does this as well, but I’m drawing a blank on how to proceed.

I have an Invoice database with 14 line items per record. Often I have occasion to search a date range of Invoices looking for the presence of a particular product, and it could appear on any line item. It’s easy enough to retrieve the invoices in a date range, and lineitemarrays make it very efficient. I can construct a list or array of the Invoice numbers that contain this item as I process the records, but I’m stuck trying to build a Select command that would then select just those invoices for view. I can think of 2 different ways to do this, but neither is the least bit elegant and I wonder if there is a better way.

One way:

Select «Invoice» = list_item_1 or «Invoice» = list_item_2 or ...

(this could be a long list)

Another way:

Select «Invoice» = list_item_1
SelectAdditional «Invoice» = list_item_2
...

The list_item could be an array or any other construct that I can build as I parse the original date range data. Is there a way I can use this array or another form of list in a one-step Select statement, something like:

Select «Invoice» = list_item_array

The «Invoice» field is numeric.


#2

See page 414 of the Formulas & Programming.pdf under “Help”, specifically “Exporting Line Items as Separate Records”. Basically, you can make all the line items in multiple invoices into individual records for easy manipulating and selecting.

Jeff Kozuch
President, Acacia Systems
Panorama Programming and Training
Apple Certified Technical Coordinator
Member, Apple Consultants Network
jk@acaciasystems.com
http://www.acaciasystems.com
562-437-7690


#3

If the list items are in a carriage return separated text array in a variable named listitems, I believe this will do the trick.

select arraycontains(listitems,str(Invoice),cr())

#4

Jim, there is some logic in your solution that would have escaped me for a long time. This is why we keep you around!:grinning: It does exactly what I wanted.

By the way, I indented the code sections in my original post 4 spaces, expecting it to “look like code” like it does in your post. Oh, maybe I discovered this earlier - the code lines have to be separated from other text before and after with a blank line? Is that the rule?


#5

Yes. And to make sure I was correct about this, I went back and corrected your original post :slight_smile:

I was hoping there was some reason!


#6

You mentioned in another post a while ago a more automated way to indent a section of code, by selecting it and … doing something.


#7

In Panorama, you can do this by pressing Command-]. This also works in BBEdit, Xcode, and probably some other programming editors. Press Command-[ to unindent. Unfortunately, it doesn’t work in the Discourse editor (of course a web based editor has no way to work with command keys). Often I’ll copy the text to one of these editors just to do the indent.