I have an order form in one database and an invoice in another. Both were converted from Pan 6. When I copy data over in a procedure any blank numeric records from the order form now come in as zeros in the invoice. I’ve tried variations of zeroblank but nothing seems to work. I think the offending statement is: speedcopy “Q1”, “P20”, “Q1”
How can I get numeric fields with no data to import as blank and not as zeros?
I’ve had the same problem with PanX insisting on inserting a $0 entry when I just want a blank. In a set of records where people have cancelled convention attendance, I have always blanked out no-longer-needed info simply by copying with a macro the field above and moving right – COMMAND-0, FWIW. Been doing it that way for 6 or 9 years. But now, even where the above field is blank, I get the unwanted “$0” so I have to remember not to use my trusty COMMAND-0, but instead actually copy/paste the blank field. I’m up to remembering to do this about 2/3 of the time.
The same issue seems to occur when doing summaries. Blank $ fields become $0.00. I also go back and have to copy and paste a blank for Debit and Credit summary fields with no values.
Obviously not acceptable. So can anyone suggest a solution to this? Is it a bug or a feature?
You can put this into the Code pane of the field Properties panel of any field you want to blank out 0’s:
formulafill ?(«»=0,zeroblank(«»),«»)
This will blank out any manually entered 0 along with any other existing displayed zeros in that field but not help with entries made in other ways. This can be covered when using procedure entries by adding this code after the entry code as in this example:
myField=0
runfieldcode myField
This will force the field code to execute the same as if entered manually. You could also make a hotkey to run zeroblanking code any time on all the numeric fields in any database even without the above entry into the Properties Code pane.
definehotkeys "Global", "OPTION-CONTROL-0", |||local myNumericFields
fieldtypes "",myNumericFields
arrayfilter myNumericFields, myNumericFields, cr(),
?(arraylast(import()," ") contains "NUMERIC",
arrayfirst(import()," "),"")
myNumericFields=arraystrip(myNumericFields,cr())
looparray myNumericFields,cr(),element,index
field (element)
formulafill ?(«»=0,zeroblank(«»),«»)
endloop|||
I have chosen the option-control-0 hotkey but you can substitute any combination you want. You can simplify the code to only act on the database and fields you want with code like this run from the desired database:
definehotkeys "database", "OPTION-CONTROL-0",
|||let myNumericFields="Price,Quantity,Total"
looparray myNumericFields,",",element,index
field (element)
formulafill ?(«»=0,zeroblank(«»),«»)
endloop|||
Note: What would be cool is if there was a pattern designator that indicated zeroblank. Maybe using a special character like § (option-6) so that pattern(myPriceField,“§$#.##”) would indicate that the Price field entry would be blank if it was zero and this pattern could be entered into the Output Pattern of the numeric field Properties.
You are correct, in Panorama X speedcopy does not preserve empty numeric values (vs. zero). In previous versions of Panorama the speedcopy statement was very fragile and it was easy to create code that would crash or corrupt the data. Panorama X was rewritten using a different method that eliminated those problems, but it no longer handles empty numeric values. This is a bug and I have written it up.
You probably need an immediate fix, so I would suggest switching to using lookupmoredata( instead of speedcopy, with the zeroblank( function for numeric values, like this.
Address=lookup("Customers",Company,Company,Address,"",0)
City=lookupmoredata(City,"")
State=lookupmoredata(State,"")
Zip=lookupmoredata(Zip,"")
Price=zeroblank(lookupmoredata(Price,""))
This will be just as fast as speedcopy.
I just verified that Panorama has always worked this way, this is not a change in Panorama X.
Since you didn’t provide the code of your trusty COMMAND-0, I cannot really help with this one. But COMMAND-0 is definitely not a built in Panorama command, it must be something you have written. It can probably be adjusted to work properly.
Actually even simpler code will work, the ?( function isn’t necessary.
formulafill zeroblank(«»)
I don’t think usually you would want to put this in the Code panel though, you probably don’t want to do this for the entire database each time one cell is modified. If you want to make sure that a zero is always entered as an empty cell you could use this code in the Code panel.
«» = zeroblank(«»)
Option-6 is already taken, but a variation on this could be an interesting idea.
How about ø (option-o)? Seems to suggest zeroblank to me.
Actually in Pan 6 I had something similar to the code solution above which did transfer over to the converted Pan X database. Each of the fileds concerned has this in the code box:
AΩ=zeroblank(YΩ*PΩ):total=zeroblank(sum(“AΩ”))
Maybe I need to add to this so that the various cells named Y and P (Y1, Y2, Y3… P1, P2, P3, etc up to P20) would display blanks. I tried this but no luck:
«» = zeroblank(«»):AΩ=zeroblank(YΩ*PΩ):total=zeroblank(sum(“AΩ”))
This worked very nicely for the summaries as well. Thanks for suggesting this.
In Panorama X you have to get rid of the : between the statements, so try this:
«» = zeroblank(«»)
AΩ=zeroblank(YΩ*PΩ)
total=zeroblank(sum(“AΩ”))
I have put them on separate lines, but that is not necessary, a space will also work.
No difference, I still get the zeros.
Here is the entire procedure, maybe this will help.
local temp,tempname,x
«Sold to»=grabdata("Order form","Sold to")
«sold1»=grabdata("Order form","sold1")
«sold2»=grabdata("Order form","sold2")
«Soldcity»=grabdata("Order form","Soldcity")
«Soldstate»=grabdata("Order form","Soldstate")
«Soldzip»=grabdata("Order form","Soldzip")
«Soldcountry»=grabdata("Order form","Soldcountry")
«Ship to»=grabdata("Order form","Ship to")
«ship1»=grabdata("Order form","ship1")
«ship2»=grabdata("Order form","ship2")
«ship3»=grabdata("Order form","ship3")
buyer=grabdata("Order form","buyer")
Shipvia=grabdata("Order form","shipvia")
oord=grabdata("Order form","Our order")
cord=grabdata("Order form","cord")
Terms=grabdata("Order form","Terms")
Due=grabdata("Order form","due date")
fob=grabdata("Order form","fob")
Quality=grabdata("Order form","quality")
Design=grabdata("Order form","design")
description=grabdata("Order form","description")
gamme=grabdata("Order form","gamme")
quantity=zeroblank(grabdata("Order form","yds/mts"))
price=zeroblank(grabdata("Order form","currency"))
temp=zeroblank(lookup("Order form","Our order",oord,"Our order",0,0))
speedcopy "Q1", "P20", "Q1"
A1=zeroblank(Y1*P1)
A2=zeroblank(Y2*P2)
A3=zeroblank(Y3*P3)
A4=zeroblank(Y4*P4)
A5=zeroblank(Y5*P5)
A6=zeroblank(Y6*P6)
A7=zeroblank(Y7*P7)
A8=zeroblank(Y8*P8)
A9=zeroblank(Y9*P9)
A10=zeroblank(Y10*P10)
A11=zeroblank(Y11*P11)
A12=zeroblank(Y12*P12)
A13=zeroblank(Y13*P13)
A14=zeroblank(Y14*P14)
A15=zeroblank(Y15*P15)
A16=zeroblank(Y16*P16)
A17=zeroblank(Y17*P17)
A18=zeroblank(Y18*P18)
A19=zeroblank(Y19*P19)
A20=zeroblank(Y20*P20)
total=zeroblank(sum("AΩ"))
In the meantime until the speedcopy bug is fixed I’m adding this procedure which will clear the zeros
local F
for F,1,20
field (“Y”+F)
formulafill zeroblank(«»)
field (“P”+F)
formulafill zeroblank(«»)
endloop