commit record, and duplicates

[innodes]

We have a sku number generator set up with all the fields being required before moving on. This is done in the Define Data / fields settings. One field is automatically calculated with a sequential sku number unique to each file. We added a "commit record" line in the script to force a save as soon as the sku number is created. The user then has to finish entering the required fields before moving on. The goal was to prohibit duplicated numbers being created if two users were accessing this tool at the same time. It seemed to be working, but then we caught a duplicated sku number. It seems that the "commit record" in the script is not committing until all the required fields are complete (a requirement). Is there anyway to force a commit, so the record is saved, temporarily overriding the field fill requirements (invisible to the user), but then bring the user back to the fields for completion?

Here is what we have today in the "add sku" script:

New Record/Request

Allow User Abort [Off]

Set Error Capture [On]

Commit Records/Requests [No dialog]

Set Error Capture [Off]

Any help is appreciated. We really need this running and not allowing duplicate numbers.

thanks

Edited November 1, 2007 by Guest

[bcooney]

Is the SKU field set to auto-enter a serial, validate unique, and prohit modification. If so, you will always have a unique SKU, no scripts needed. Even in a multi-user environment, FM will take care of your requirement that SKU is unique. What are the settings on SKU?

[innodes]

Sorry for the time lapse, I've been under the weather over the past week.

The settings on the sku are:

Indexed, Auto-enter Calculation replaces existing value, Evaluate Always, Can't Modify Auto, Always Validate, Unique

[bcooney]

Why is it an auto-enter calculation and not just a serial number? Big rule of thumb, key fields are meaningless serial numbers!

[innodes]

The reason it is calculated is that it is not a key number. It is actually a product number. We need to keep them in sequence, without skipping numbers. A serial number, counts every trial, deleted or not, which gives us blanks and missing numbers. It needs to check the existing set of records, then create the next in order.

This specific example is straight forward, as K####. However the next portion I have to build gets more complex. There are variations dependant upon the product and customer. For example a H068H, U068H and A068H are all very different items. There are additional complexities, but if we can get the file to lock down, those can be dealt with.

Any thoughts?

[bcooney]

These puzzles aren't my strong point. I've never created by calculation unique values. LaRetta, Vaughan, Comment, are you willing to take this on? I bet there's already a post that answers this question, although I couldn't find it.

[David Jondreau]

Well, the Set Error Capture [On] is good to have if you're checking for Unique values in the field definition, but you need to actually trap for it with a Get(LastError).

Something like...

Set Error Capture[On]

Loop

Commit Record

Exit Loop If[Get(LastError) :notequal: 504]

Show Message["That SKU Num is being used, please enter a new one." Add the SKU field here too]

End Loop

Set Error Capture [Off]

[innodes]

Thanks,

I got pulled to another item. I will get back once I've had a chance to try these out.

[bcooney]

The reason it is calculated is that it is not a key number. It is actually a product number.

So, you have a serial number unique key field for use in relationships, and this is just a display?