Selecting Line Items

Am I wrong to think that

Select ProcedureCodeΩ beginswith “J”

should be the same as:

Select ProcedureCode1 beginswith “J”
or ProcedureCode2 beginswith “J”
or ProcedureCode3 beginswith “J”
or ProcedureCode4 beginswith “J”
or ProcedureCode5 beginswith “J”

Using the Ω gets an error that the field does not exist.

I do not get that error. Is it perhaps due to smart quotes?

Have you tried putting it in chevrons «ProcedureCodeΩ»

The smart quotes show up here but they’re not in the procedure.

I thought that it had been working, then it did not. What I just now discovered is that it requires the Datasheet to be active. In a form it fails.

Screenshot 2022-12-06 at 11.09.43 AM

My experiments show using a lineitemΩ in a select formula seems to only check the currently active field. If that field is a line item it will only check that field. If the current field is not one of the desired line item fields it will return an error that the field does not exist. Bug or feature…you tell me.

Yup, you’re right Gary. When I activated the Datasheet I had also activated an applicable field. Any other field gets that error.

Yes, you are :wink:

What you need is the lineitemarray( function. To select any line item that contains “J”, do this:

select lineitemarray("ProcedureCode",cr()) contains "J"

To match only when “J” is the first letter is a bit more complicated. Here’s one way to do it.

select chr(1)+lineitemarray(“ProcedureCode”,chr(1)) contains chr(1)+“J”

The Ω suffix does not expand to multiple fields. Instead, it allows you to use a single formula for multiple line items. So the formula

QtyΩ*PriceΩ

will work no matter what line item field is active (and only if a line item field is active). So if the currently active field is Item4, the formula above will be interpreted as:

Qty4*Price4

In your original code, you used:

Select ProcedureCodeΩ beginswith “J”

If you are currently in field ProcedureCode7, this will be interpreted as if it was:

Select ProcedureCode7 beginswith “J”

But if you are currently in the field Address, the result will be an error, because there is no current line item field.

I did try the lineitemarray( but with 30,000 records it was terribly slow compared to

Select ProcedureCode1 beginswith “J”
or ProcedureCode2 beginswith “J”
or ProcedureCode3 beginswith “J”
or ProcedureCode4 beginswith “J”
or ProcedureCode5 beginswith “J”

This is pretty quick; I had simply suspected I could make it faster still with Select ProcedureCodeΩ beginswith “J”.

This has actually been noted before as a possible area for future improvement.