ArrayBuild and LineItems

Hi,

I’ve seen posts that LineItems cannot be used in certain situations … like, in ArrayBuild.
(See: Lineitemarray not working with arrayselectedbuild)

So, I don’t expect this to work, since the line items PaymentΩ are specified in Sum().

Let lvB = ArrayStrip(ArrayBuild(¶,"",
        {
            Str(«Inv#»)+";"
            +Pattern(Sum(PaymentΩ),"$#,.##")+";"
        },
        {
            «CustID» = lvCustID And Abs(Val(«Balance»)) > 0.0001
        }),¶)

DisplayData lvB

However, I would have expected something like this to work, but it also fails.

Let lvB = ArrayStrip(ArrayBuild(¶,"",
        {
            Str(«Inv#»)+";"
            +Pattern(«Payment1»+«Payment2»+«Payment3»+«Payment4»+«Payment5»+«Payment6»,"$#,.##")+";"

        },
        {
            «CustID» = lvCustID And Abs(Val(«Balance»)) > 0.0001
        }),¶)
DisplayData lvB

And, if I just put «Payment1», the same thing occurs.

Just for grins, I ran the following, and they did display as expected.

displaydata Sum(«PaymentΩ»)
displaydata Sum(«Payment1»)

Could you be more specific as to what “fails” means? Was there an error message? Empty or wrong value? What?

In Panorama 6 the lineitemarray( function did an extra special “trick” when used with arraybuild. In Panorama X this trick is not implemented (and I think I may ultimately implement this function a different way). Other than that, I don’t know of any issue with line items.

Manually adding the payments, as in:

Pattern(«Payment1»+«Payment2»+«Payment3»+«Payment4»+«Payment5»+«Payment6»

Yields a total of “0” … instead of the actual, non-zero, sum of all the payments.

For example, in the record in question, «Payment1» is 300 and «Payment2» is 60, and the rest of the «PaymentΩ» values are 0, so the total should be 360. But, they are returning 0 in the two lvB = ... examples, above.

Whereas, these calculations:

result in 360 and 300, respectively.

You may be rephrasing it, but to be sure, that should be $0.00 or preferably $360.00. If in fact it’s 0 it would suggest something is amiss in the pattern.

Sum(«Payment1») shouldn’t work at all. «Payment1» would be interpreted as the root name of the Line Items so Panorama would be attempting to use «Payment11», «Payment12», «Payment13»…

You want either sum(«PaymentΩ») or sum(«Payment»). Both are working for me with a simple database I set up using your field names. As is

pattern(«Payment1»+«Payment2»+«Payment3»+«Payment4»,“$#.##”)

I have a test database with line items. So I made a simple test procedure:

let totals = arraybuild(cr(),"",{str(Price1+Price2+Price3)})

This worked fine, I got a text array with the totals for each record.

I then tried it with the pattern( function.

let totals = arraybuild(cr(),"",{pattern(Price1+Price2+Price3,"#.##")})

This also worked fine.

FInally, I tried it with the sum( function.

let totals = arraybuild(cr(),"",{sum("PriceΩ")})

This did not work - it gave the total for the currently active record over and over. I haven’t looked at the code yet but I’ve seen this issue before and the fix is probably fairly easy.

I’m not sure why you are getting zeros but there must be something wrong on your end, the basic feature does work.

The sum( function is now fixed for use in arraybuild(, formulafill, select, etc. The fix will be in the next release.

So … i’ll try this again … I would expect it to work, but it did not the other day.

Does it make a difference if you put the chevrons (“«” and “»”) around the field names?

(I’m sure it’s something I’m doing … :roll_eyes: :face_with_open_eyes_and_hand_over_mouth: :joy:)

Cool. Thanks!

Concur! Thanks, James.

The chevrons are required if the field name has punctuation or special characters in it. None of your field names have special characters, so the chevrons are optional. They won’t hurt anything though (when used in normal calculations).