Improting and record IDs

[jim lee]

Now this is really driving me batty. I have a script to import a table. Simple enough. I clear out the table, import all the records with no updating from a standalone. After the import, I need to reset the auto serial number for the record ID.

Import Records[no dialog, add, roman]

Next Serial Value [transactions::transaction ID,Max(transactions::transaction ID) + 1]

Bring in the records, look for the biggest ID, add one and there you go.

I see the table populate, but still I get 2 as the serial value. Looking at the data it should be 5. I even tried adding a "commit Records[]" step between the two and that didn't help either.

Hints? Help?

Thanks!

-jim lee

Edited September 29, 2006 by Guest

[John Mark Osborne]

Could you tell us what you relationship and key fields are for "transactions" and what your scripts steps are.

[jim lee]

Well, there's not much to tell. The script steps I did list

Inport[]

Set Next Serial Value [transaction::transaction ID,Max(transaction::transaction ID)+1]

As for key fields, the transaction ID is the main one that most things link to. There is another trans com that ponts to another table holding common data like value & description.

All the calling script is doing is setting the correct layout [transactions] and then calling this 2 line script. (Along with a list of other tables doing exaclty the same thing)

I tried calling this script by itself and it still just don't work. I can see in the debgger that the Max(transcation ID) thinks the answer is 1 until the script is completed. Then it jumps to 4.. Sigh..

-jim lee

[comment]

Try it this way:

# DELETE ALL RECORDS

# IMPORT

Sort Records [ Specified Sort Order: Transactions::TransactionID ; ascending ]

Go to Record/Request/Page [ Last ]

Set Next Serial Value [ Transactions::TransactionID ; Transactions::TransactionID + 1]

[T-Square]

I suspect your trouble has to do with the way you're using MAX. THe MAX function works on an aggregate of related records; if you use the MAX calculation based on the current TO, it only has the current record to work with. If you create a second TO for the transactions table and make a cartesian selfjoin to itself, the MAX function should work the way you're expecting.

If you're already doing this, perhaps there's a flush that might help. But here I'm speculating wildly.

David

[jim lee]

Well, doing the sort then pick the last record did the trick. Thank you -very- much. Never thought of doing it like that. The Max() function did the right thing, I could see it in the debugger, just it did it -after- the script exited.

Now, here's one last question.. This file has about 7 tables in it. How can I set it up so the user only has to select the source file one time? Right now each table has its own inport step and throws up a select file box.

Thanks again!

-jim lee

[T-Square]

You can script it. Otherwise, you have to handle each table separately.

[jim lee]

This is scripted, I use the import records script step. Each time I do it asks the user for a file. I'd like to have "use last file" option or something.

I must be missing something really obvious here.

-jim lee

[DukeS]

I have answered this few posts ago in topic "multiple table import...". You just have to have global field (named FileName for example) in both source and destination files and you first import globals table (with open file dialog) with your global field "FileName". In next script steps you set variable $_filename with field contents of "FileName" and use this variable in following import record script steps.

It sounds sooo confusing I know but I can't write it other way.

[Genx]

Sorry, why aren't we just using GetNextSerialValue() here?

Secondly, is your field a text field, or a number field?

[jim lee]

I don't follow how GetNextSerialValue() helps. What is it you have in mind?

The field type is a number field.

What I'm doing is taking an empty database and filling it with and earlier version of the same thing. (Full of data) This is why I'm bumping up the serial numbers so new records can be added.

-jim lee