Batch_begin/end with multiple input

[CALocklin]

Does anyone know of any issues with using Batch_begin()/Batch_end() when dealing with multi-user input on a network?

I'm still trying to solve some problems with periodic "cannot resynch on index" and other issues with 4 people doing lookups and data entry on the same table. Just to add to the load, some of the input is even handled automatically by Xbasic. One recurring issue happens when two people happen to be creating a new order at the same time. An automated routine enters up to 10 line items for an order and sometimes puts user A's line items in user B's order leaving user A with no line items and B with a double set of line items! I've confirmed that both orders have different order numbers.

I'm thinking of using Batch_begin()/Batch_end() to see if it will fix the problem.

Cal

[Tom Cone Jr]

RE: Batch_begin/end with multiple input

Cal, I haven't heard of any difficulties (that I recall at the moment). It occurs to me that you might want your script to enter multiple new records with empty fields, save for the linking fields in each. Then have your script go back and 'change' each one at a time. You may want to batch both the new Enters, and the Changes.

In worst case you may want to have your script open the table exclusively, add the new records, with link fields populated only, and then close it. Then go back and change them while using the table in shared mode.

-- tom

[CALocklin]

RE: Batch_begin/end with multiple input

The problem with opening exclusively is that there is almost always someone else viewing or entering data in the same tables. Using the exclusive mode would virtually put them back to a single user system.

"enter multiple new records with empty fields ... Then have your script go back and 'change' each one at a time" That's one possibility but I hope it doesn't come to that!

I've tried so many things - I just hope I will know exactly what solves this problem once it's actually solved! If I can't get it this week, we are going to see what happens by going to Win2000 instead of Win98.

Thanks,
Cal

[JohnZaleski]

RE: Batch_begin/end with multiple input

Cal,

If you have an automated routine that adds a bunch of line items to an order automatically, is it just the indexes that get messed up or is it the actual data in the record. If you are using table methods to add the automatic line items, I don't see how the linking field ( I assume it's an order number ) could get messed up because it would be the script that adds the line items ( including the linking field ).

My guess is that the batch begin and batch end would not function properly while others were trying to add or change records. I believe the reason that they add speed to a process is that the temporarliy assume complete control of the add change access to a table.

John

[Tom Cone Jr]

RE: Batch_begin/end with multiple input

Cal, I've been thinking about different ways User A's order info can wind up in User B's record, and vice versa. Truly a puzzle.

1) first guess is that there might be a network issue. Latency over the net may be causing each work station to fall out of synch with what's actually happening at the server. The programs think they know what's happening but in the real world the data are not making it to the server safely. Are they using fast reliable network cards? I know almost nothing about network design, but others have suggested that using a switch instead of a router is often necessary in multi-user high-input situations.


2) next guess is that the logic of the automated scripts (those which add up to 10 child record line items automatically) may be assuming that the table is static throughout the update... except for changes being written by the script itself. This won't be true in multi-user environment. For example, the script might assume it's ok to pull values from the last record in the table and use those in new records. Works ok in single user setting. Would cause a problem if another user is firing records at the table at the same time as the script is executing. Even though you've probably done this a dozen times, I'd tear the script apart line by line and ask myself, after each line, "how will this work if someone at a different work station has just stored another record in my table?"

Good luck troubleshooting your problem. Let us know how it turns out.

-- tom

[Daniel Weiss]

RE: Batch_begin/end with multiple input

Hi Cal
I have a mail order application, which is being used by 8 different clients; my clients generally have between 20 to 40 workstations. All except one of my clients the salespeople don�t enter there sales into the system they just write it on a sales from and they have about 3-4 girls entering the invoices in to the system. The salespeople just use the system to lookup product and prices. These clients I don�t run in to many problems they are running fairly good. The big problem I have is with the client that the salespeople do enter there sales into the system. Over there I have been having some problems since the beginning but for the last two weeks it�s a disaster first with "cannot resynch on index" then not finding invoices and memo fields getting corrupted (the memos are in a separate table attached as a child to the invoice header) etc. etc. I can�t really blame the network; they have new dell server an Intel switch and all dell workstations.

I am out for any help and advice, if you come up with something good please let me know.

I am thinking if it would be a good idea to make duplicate tables and sets and use one set for new entry�s only and the second set changing only, this would require a script to append all new records to the second script. Does anyone this could work?

Any help will greatly appreciated

Daniel Weiss

[Robert Adler]

RE: Batch_begin/end with multiple input

Cal:

I experienced a problem with data for the child ending up attached to the wrong parent. I found that the linking key was not unique enough. The original relationship was Batch no to batch item number(1 to many). Periodically the batch number was reused and items shifted for two batches. To correct the problem I created a more restrictive link. Using batch number and batch date + item number.

The problem has been eliminated.

I hope this helps

bob adler

[Robert Adler]

RE: Batch_begin/end with multiple input

cal:

sorry about the last message. the key was batch no+date+time. this eliminated the data shift. between parent and children.

bob adler

[csda1]

RE: Batch_begin/end with multiple input

Hi Cal,

Batch_begin() and Batch_end() just temporarily give you access to the table without other users competing for record locks during the operation. I believe records that are locked by other users prior to the batch_begin() will still be locked afterwards. It does not give you temporary exclusive access, just merely temporary access to the table without any new operations being applied to the table from other users.

This has an important aspect. If a user is in change mode in a set and has referential integrity (and perhaps add/change child records) set, all of the children records are locked out for change from any other user.

If a user is in enter mode with referential integrity, you can not add records based upon his link field to a child until his record is saved, or you must pass the linking field to the adding child record routine.

This has another consequence. If you have an auto-increment field, and two users are in enter mode, they each have the same value of the auto-increment field. At save time, the value of the field is reevaluated and the may be incremented again if one of the users already saved. If your adding child record routine added records based on the autoincrement field while they were still in enter, the link may have been wrong. You need to use the value after a save. Best way is, prior to entering any children by any method, save the record, go into change mode, then continue. This has consequences of default values in the field rules not operating for change mode, but events can be used to handle these if required.

Does this make sense to you, ordo I need to clarify?

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Daniel Weiss]

RE: Batch_begin/end with multiple input

Thanks Ira

Sorry for bogging in but I share the same problem as I wrote in the previous message
The problem I have with "cannot resynch on index" is not happing in the invoice header, it is happing when the users are entering or changing the line items. I do have my invoice header which uses a auto-increment field set up that it saves as soon as a new invoice is entered, just like you mention. But when they enter or change in the line items other items could pop up or the "cannot resynch on index� shows up and then slows down the system, invoices or customers are not found. Also at some cases an invoice wont show up on one computer but will show up on an other computer
Thanks for help and can you please explain the flowing in more detail

�This has an important aspect. If a user is in change mode in a set and has referential integrity (and perhaps add/change child records) set, all of the children records are locked out for change from any other user.

If a user is in enter mode with referential integrity, you can not add records based upon his link field to a child until his record is saved, or you must pass the linking field to the adding child record routine�

Thank you again

Daniel Weiss

[csda1]

RE: Batch_begin/end with multiple input

Daniel,

When referential integrity is on for a link, the moment a parent goes into change mode all of the children are locked against having changes performed by anyone else. Other users/processes can read the record, but not write it.

My guess is that similar linking field valued records added by other users/processes at that moment would be able to add "child" records, although the user in change mode would probably not see the new child until a resynch or maybe refresh was done on the child. I'm not even sure if it would lock any newly added child records without exiting the change and starting change again.

In enter mode, the link field does not exist until the parent record is saved from the point of view of any other process. And until it is saved, an auto-increment field's value could be changed. Thus it would be unsafe to add records automatically until the enter is finished.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[CALocklin]

RE: Batch_begin/end with multiple input

Well, at least one of my problems has been solved. It was created by another one of those Duhhh! situations.

The "User A gets User B line items" was caused because I was getting the order number in the CanSave event.

For anyone who wants to read the rest, this is a good example of why you should "keep it simple" right from the start.

1. My original version used a set which started with the Customer as the parent and then attached Orders, Line_items, and Payments. At first this seemed like a good way to find a customer and then switch to any desired order, etc.
2. In this particular situation where one "order" is actually multiple monthly orders (for preschool kits) it made a lot of sense to create the line items automatically so I created a routine to do that.
3. I soon found out that the order number could not be picked up after the Order record was saved because the record pointer goes back to the first order attached to the parent table. (IOW, If you create a second order for customer A and save it, the pointer moves back to the 'top' of that parent record. Therefore, getting the order number after the record is saved will result in getting the value of the first order.)
4. Because of #3, I changed the script to set the Current_Order as a global variable in the CanSave record event. This worked just great on my single user system.
5. Later, I changed the app to input/edit orders based on a simple set using the Order_header table as parent with a 1:1 link to customer so the user can verify the customer name when creating the order.
6. Many agonizing weeks later, after reading Ira's comment, I hit myself in the forehead and said something like, "You stupid *&$#(^!; you should have known better!"

I didn't even have to come back and check it out to realize this was what I had done.

Now, if I get real lucky this will solve all the other issues, too!!?? (No, I'm not holding my breath.)

Thanks,
Cal

[csda1]

RE: Batch_begin/end with multiple input

Cal,

Try saving the order # in the OnSave event instead. If you go into enter a record on 2 different systems, save 1, then look at the value when you save the 2nd, hopefully the value in the OnSave will be the saved value after adjustment.

It's also not clear to me why the table is repositioning itself. After the save, the record should be moved (unless some process is forcing a refresh, resynch or similar. I'll have to think about that part a bit and maybe reread your comments.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[CALocklin]

RE: Batch_begin/end with multiple input

Ira,

That's what I did; I used the OnSave event as the solution.

As for returning to the first child record, don't waste any time trying to figure it out. It is entirely possible that I was using a resynch. I did this quite a bit, along with .refresh(), to get other values/calculations to update. At this point, I have no way to know exactly what was happening at the time. (I've learned to keep only a limited number of old backups!)

Cal