I’m updating databases which have been using post “updateadd” to (very slowly) find and, if necessary, add records, with the new Join database function, which is lightning fast. The issue I have come across, is that the records in this database have a unique ID field, which is automatically created when the record is created with the post “updateadd” function, but not with the “Join” function. I assume I’m going to be able to programme in a workaround, but thought I’d draw this to your attention.
Actually, I can’t satisfactorily programme it. Because the programme doesn’t recognise the numbers that I’ve put into this field in my programme, and when a new record is created, it uses the number it was up to, effectively creating a duplicate number, which isn’t recognised, even though I have specified no duplicates in the field properties. Help!
Maybe the setautonumber statement will help you avoid the duplicates when you add a new record following a join.
No duplicates in field properties, as you have discovered, does not prevent you for assigning duplicate numbers. See the info on duplicates:
Perfect. That will help me programme a work around until such time as the “Join” automatically does it (???). Is there a neater way than using a loop to sequentially increase the number by one moving through records?
The join statement is not intended as a replacement for the post statement. The posttorelated statement would be a closer match.
You don’t explain what technique you are using to create a unique record id. If you are using auto-number, then I believe the posttorelated statement will do that for you. Auto numbering works when you are adding one record at a time. If you are adding a bunch of records at once, for example with import or join, then there is no auto-numbering.
You mention that the join statement is “lightning fast”, which it is for large numbers of records. But if you were using the post statement then to me that means you are only doing one record at a time. There is no speed benefit to using join for a single record.
Just so you know what I am doing:
I am working with my cattle database. Our cattle have electronic ID tags, which we scan to identify them. We may work with anywhere from 1 to 200 head of cattle at a time. I scan them into Numbers, adding new information as required. Sometimes I will need to replace their electronic tags, sometimes their visual tags, sometimes they are new cattle who have not previously been entered onto the database. I may want to add comments about their health, their calves, their growth etc.
When I get back to my computer, I export the numbers data as a csv file. Then I import it into a Panorama database. I have previously looped through each record in this file, using post “updateadd” to identify the corresponding record in the main database, and select it. This could take a long time, and is where I am now using “Join”, which is much faster, (but not updating the auto numbering). Maybe there was a faster way of doing what I had been doing before?
If adding multiple records can’t autonumber, that’s OK, I’ll programme that in, now I know about the setautonumber function.
Ok, I maybe understand what you are doing. When you join, can’t you just leave the auto-number field out of the relation, so that it would just be zero for the new records? Then you could go back and fill in the auto-number afterwards with code like this:
select IDNumber=0
if info("empty") return endif
let autoNumber = getautonumber()-1
field IDNumber
formulafill autoNumber+seq()
setautonumber autoNumber+info("selected")
I don’t know the name of the field you are using for auto-number, so I just used IDNumber. Substitute the name of whatever field you are actually using.
Thankyou. The +seq() is the last piece of the jigsaw puzzle I needed.