Formulafill With an Array Total Pan6

#1

Hi,

I am trying to find a way to run a formulafill that will create an array of numbers then add the numbers together to put the result into a field. I can make this happen on a line by line basis by passing the array through a python script and converting it to an integer before feeding it back to the field. Is there a way to cleanly do the same with a formulafull? Here is a sample of the code that I am running to do a line by line calculation.

global vThisItem, vTotal, vItems
vItems=""
vThisItem=str(Item)[1,3]+rootstock

arraybuild vItems,",","",?(str(Item)[1,3]+rootstock=vThisItem, str(val(str(Item)[-1,-1])*val(«bundle size»)),"") python |||x=(^«arraytopythonlist(vItems,",")»^);x=map(int,x);x=sum(x);print(x)|||,orderedtodate

Thanks for you help,

Ken

#2

Checkout arraynumerictotal( in Help.

#3

I am not certain I fully understand exactly what you are trying to accomplish but I will take a stab at it. I assume the field you want to fill is orderedtodate and it is a text field. Here is a method to daisy-chain two array functions to use in a formulafill:

global vThisItem, vTotal, vItems vItems="" vThisItem=str(Item)[1,3]+rootstock
field orderedtodate
formulafill arraynumerictotal(
arraybuild(",","",{?(str(Item)[1,3]+rootstock=vThisItem,
str(val(str(Item)[-1,-1])*val(«bundle size»))}),",")

#4

I tried the method before and the result gives me a “runtime error in call(procedure” message which was what led me to feeding the results of the array through a python script to begin with.

#5

I should have added that I am using Panorama 6

#6

Arraynumerictotal gives me a “runtime error in call(procedure” message> I should have mentioned that I am using Panorama classic.

#7

IIRC there was a known error in arraynumerictotal in Pan6. It’s fixed in PanX. I recall having some elaborate workaround for it’s lack in Pan6, but have been using PanX long enough to have forgotten it’s details. I suspect it wasn’t doable with formula fill.

#8

I am wondering if you need the array part at all.

Pan 6 is excellent in grouping and summarizing and creating crosstabs, and you can pick values from summary cells.

On the other hand, if your data are set up in line items, not in columns, you can use functions like sum( or lineitemarray(.

#9

Interesting, I haven’t played much with crosstabs. I give it a look.

#10

I can do it with a loop but that is slow. Seeing as how we have around 70 computers running on pan 6 PanX is not in the immediate future for us.