I have a database with several fields that have not yet been titled or named. If I attempt to enter data into those fields, the data does not get accepted but is merely ignored.
I couldnāt duplicate this, but then I created a DB with 3 fields (A,B,C
as defaults), erased C as the title of the 3rd field, erased B as the
title of the 2nd, and erased A as the title of the first. The second
relabeled itself as 3 and the first as 2. They would both take data but
the one with no title wouldnāt take any. I suppose Panorama needs some
way to tell itself where youāre putting the data?
Yes, the field does have to be empty of a ānameā.
The database was the result of an import from iOS Numbers via a csv file. I did not recognize the multiple fields that Panorama X created due to the tacky/lousy exported file from Numbers but the result was several no name fields. Certainly a low priority for a bug, but Panorama should message the user when there are one or more non named fields.
I was able to duplicate this, and have added it to Bitbucket.
This puzzled me at first but actually this is intentional. Panorama wonāt let you set up two fields with the duplicate names. If you do, it adds a number on the end. So if you already have a Name field, and try to rename a second field as Name, it will change it to Name2. Since you already had a blank field, when you tried to make a second field blank, it renamed it to 2, i.e. blank with a 2 after it.
Not quite the same scenario as trying to add data to fields with no name, but I am able to create a database with fields that have no name but contain data.
Using āNew Database from Text Fileā¦ā menu command I created a database from a Quickbooks export CSV file. The CSV file is loaded with columns with no name, several of which contain data. The data in these columns imports just fine into fields with no name. In fact, all the āno nameā columns import as fields without a name, creating a slew of fields without names.
Which made me re-read this several timesā¦
As these fields donāt have names, technically these fields do not have āduplicate names.ā But it does seem that the āNew Database from Text Fileā¦ā menu command is bending the rules a bit.
All that aside, short of manually cleaning up my CSV file prior to import, any ideas for selecting all Fields with no name = empty, so I can readily delete all the empty no-name fields?
Yes, I too got a mess of an import from QuickBooks. What Iād like is that Panorama might create temporary names for fields with data but no given field name, and also ignore any extra fields that are both devoid of data and field names. Keeping that extra junk serves no purpose.
The CSV importer doesnāt check for duplicate field names. So not only could you create multiple fields with no names, you could also create multiple fields with the same non-empty name. This is actually ok (Panorama 6 allowed it), except it can cause confusion in formulas and programs that reference such fields. Thatās why when you manually create a field, it wonāt allow a duplicate. But when you import from a text file, itās up to you to fix any duplicate field names.
There is no super easy way to do this. I just tried it, and
field ""
does not work.
But this procedure will do it (I just tried it and verified that it works).
field 1
loop
if info("fieldname")=""
deletefield
endif
right
until stopped
Jim,
Thank you for that procedure. Worked like a charm!
JimA
Robert,
Try this modification of Jimās procedure below:
field 1
loop
if info(āfieldnameā)=āā
if (Ā«Ā» = āā)
deletefield
endif
endif
right
until stopped
I added a step, if (Ā«Ā» = āā), which checks that current field to see if it is empty before deleting the field. That worked for me, but I only tried it once.
Now that Iāve deleted all my āno-nameā empty fields, I canāt add a new field without a name to test integrating a fieldname step, but I presume that could be done. I notice the fieldname formula seems to only provide for adding a fixed name, but Iām sure thereās some way to generate names on the fly.
Anyway, at least with what we have here we can clean out all those empty no-name fields. Progress!
ā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢EDITā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢ā¢
I just tested this on a new database and it doesnāt work. if (Ā«Ā» = āā) doesnāt do what I thought it did. Back to scratchpad.
Checking to see if the first record has data in that no name field opens up the liability of having data in other records in that field. The contents of that field for the entire database should be checked before deleting the field. This should be handled by Panorama during the import procedure.
Iāve been futzing about with combining count and countsummaries statements and functions, thinking if I insert a count statement into the loop, the procedure could identify fields where info(āfieldnameā)=āā AND the summary count is ā0ā, and then deletefield.
In other words, the procedure would select for fields with no name that are empty, then delete that field.
But, no success.
Before running the procedure with the deletefie statement, I wanted to first figure out if I could select for fields that have no name AND are empty. In this vein, here are two attempts to get the procedure to stop if the count summary = 0
field 1
loop
if info(āfieldnameā)=āā
count
stoploopif countsummaries(0)=ā0ā
endif
right
until stopped
field 1
and
field 1
loop
if info(āfieldnameā)=āā
local sumcount
count
sumcount = countsummaries(0)
stoploopif sumcount=0
endif
right
until stopped
field 1
Count works fine, only counting records in fields that have no name. But I canāt figure out how to get the procedure to acknowledge when the count summary = 0.
Hereās a screenshot of a segment of the datasheet and summary records. The summary records with 66, 2, and 0 values are all fields with no names. The empty summary records, such as that date field, all have field names.
Am I on the right track?
How about:
loop
If arraybuild( Ā¶, "", {Ā«Ā»})="" and info("fieldname")=""
deletefield
Else
right
Endif
until stopped
field 1
Check out automaticfieldname(ā¦
Gary,
Wow.
Iām diving back into the documentation to brush up on my thin grasp of arraybuild to better understand how this works. I think I get it.
And thank you for the automaticfieldname function. Iām off to the races.
Thank you!
arraybuild( is awesome, I use it all the time.
In this particular application, another option would be the aggregate( function. This formula will return true if the current field is empty (has no data), no matter what type of field it is.
aggregate({sizeof(info("fieldname"))},"maximum") = 0
If the database is large the aggregate( function will probably be faster than arraybuild(, especially if the field is not empty. The arraybuild( is taking time to generate a potentially large array that we really donāt need, aggregate( avoids that.
1 Like
@RAmeeti - Robert,
Garyās procedure works like a charm to delete empty no-name fields. (I havenāt tried Jimās aggregate method yet, mostly because my database is relatively small, but thatās on the spare time upgrade list.)
In my case, Iām still left with three no-name fields that contain data. In the time it took to write this I could easily go through the database and edit those field names, but in an effort to make progress towards automating the whole process I find the following works to isolate those remaining no-name fields:
for fn,1,info(āfieldcountā)
field fn+0
local zfieldname
zfieldname =info(āfieldnameā)
stoploopif zfieldname =āā
editfieldname
endloop
field 1
Itās not perfect, but it does scan field names left to right, stops at each field missing a name, opens the field name properties window, and puts the cursor in the name field. After giving the field a name I can run the procedure again and it moves to the next empty field and repeats.
Would be nice to trigger a repeat of the procedure upon entering the field name and confirming it by pressing Enter (Return), but for now this is micro-progress.
I just tried the aggregate( alternative and found at that it does not work with a field that has no name so that even if the field without a name had data inside it would still return 0 and end up being deleted.
This can all be handled all at the same time as the rest with an addition to the original code:
loop
If arraybuild( Ā¶, "", {Ā«Ā»})="" and info("fieldname")=""
deletefield
Elseif info("fieldname")=""
setfieldproperties initializedictionary("NAME",automaticfieldname("Test"))
right
Else
right
Endif
until stopped
field 1
Note that I have added a sample prefix for use with the automaticfieldname( function which you can change as needed or omit if desired.
I didnāt think of that. Now that you mention it, Iām surprised that the arraybuild( formula did work.
To automatically give a name to all of the untitled fields, how about this code, which should name them Untitled1, Untitled2, Untitled3 etc.
for fn,1,info("fieldcount")
field fn+0
if info("fieldname")=""
fieldname automaticfieldname("Untitled")
endif
endloop
field 1
Of course if you donāt like Untitled you could pick a different prefix.
Just for educational purposes, a couple thoughts about your code. First of all, this code:
local zfieldname
zfieldname=info("fieldname")
in Panorama X could be replaced with 1 line
let zfieldname=info("fieldname")
However, in this case the variable is unnecessary, you could have just said:
stoploopif info("fieldname")=""
Jim -
Thank you for the tutorial at the end there. Itās very much appreciated.
I had looked at automaticfieldname, but mistakenly thought it only applied to new fields. Thank you for showing me otherwise.
JimA