# Replacing value in all empty fields

In order to average a line item field accurately, I need some fields to be empty (e.g. there are 4 fields; 2 contain 100 and the other 2 are blank. The average needs to be 100, not 50). But occasionally, when I want to procedurally add a new field, I need the empty fields to have a value, so the field count will reflect the actual number of fields. Once that’s done, I need to re-empty those fields that were filled with the arbitrary value so that the averages calculate correctly again. It’ll be ok to do this switch for one record only. I’m sure the answer to this is Programming 101, but I’m stuck. Help, please.

Answering the first part of your question: The Pan X Help says the count( function gives you the number of non-empty instances of a line item field. So to calculate an average of the non-empty fields should be easy.

Regarding the second question, I am wondering why you need to count the (non-empty) line item fields. Adding a new field using the Omega suffix should adjust the field name (number) automatically.

Well, I can’t get Kurt’s solution to work and just end up with a new field named with the omega symbol added on the end and not the number. My solution is a rather robust formula to get the next line item number. Here is the code I came up with to add the next numbered line item for a field named AmountΩ:

,¶,{?(striptoalpha(import())="Amount",import(),"")}),¶),¶)+1)

This simply gets a list of all the fields and filters it to only the line item fields for the desired root name and then strips that array of empty elements and gets the size of the remaining array and adds 1 to that for the final new line item number. This is then converted to text and added onto the root field name and executed with the addfield statement.

I had the same problem with Kurt’s solution, and by the way, in the Menlo font Option-Z gets you not Ω but a question mark superscript. The only way I can get Ω is by pasting it from Font Book. Annoying.

Gary’s solution works, of course; it seems really complicated and I would never have figured it out by myself. Thank you, Gary.

My first thought had been to use count( to get the number of fields, assigning it to a variable, but it only worked right if all the fields had something in them. I cobbled together an solution using a dummy record at the beginning of the file with 0 in all the fields, but then I couldn’t figure out how to reference the new field to fill it with a number; I had successfully converted it to float type by using my variable (e.g. field "newfield"+variable fieldtype "float", but when I tried to do the same move to give the field a value, no matter what value I tried to put in there I got the error “There is no field or variable named 0”.

You might want to check your Keyboard preference to see it it is set to US. When mine is set to ABC-Extended I get the question mark instead of the omega symbol as well. If you have the Keyboard input icon in your menu bar you can change it there as well.

Yes, that was it. I had mine set to SpellCatcher Unicode. Thanks.