"Resynch on index" - v8 experiences?

[CALocklin]

I'm running into a strange situation where 1 of 7 companies using one of my generic applications is getting a "cannot resynch on index" error up to 20% of the time. The fact that others are not having problems indicates to me that the basic script is OK (besides, how many ways are there to modify a Lookupc() function?) but something is either going on with their network or there is some strange issue that starts with more network users or larger data files.

My main question is whether anyone has any 'comparative' experience with a "cannot resynch on index" error between version 7 and version 8. Is version 8 any less susceptible to this issue? The program is currently in version 7 and one of these days I'd like to upgrade it to v8 but, due to the complexity of the app (517 scripts/functions with 89,000 lines of code - a lot of verifying to be done), I'm in no rush unless I find a definite need.

If you have other suggestions, I'm listening. Just remember that the current script works fine for 6 out of 7 companies so the basics have been proven correct.

FACTS:
- This happens in the OnSave Record event when saving work orders. A lookupc() is being used to make sure the agent name matches the agent number because imported data and modified agent records can cause mis-matches. (No data is changed. Only a message is displayed if there is an issue.)
- Once it happens, the only way they can stop it is to close the app and restart it.
- When it happens, it happens only on one computer. Everyone else continues to run correctly. (This seems very strange to me but I have verified that there are no local indexes that could be causing the issue.)
- None of the other companies have experienced the problem. (I've specifically asked 3 of them.)
- None of the other companies have quite as much data in their overall application but the lookup is just on the Agents table which has about 11,000 records with a file size of 5.5 Meg. - not small but not huge either. (The next largest user has about 5,000 records - roughly half the size.)
- This company has more users - almost always 2 and up to 4. Most other companies have 1 to 2 users and occasionally 3.
- The error occurs in the following lookup:
anamef = lookupc( "F", anumb, "Agent_namf", A5.Get_Path() + "\agents.dbf", "Agent_Nof" )
- The "Agent_Nof" index was created by the system for autoincrementing the Agent_nof field but this is just a lookup and adding new agents is relatively rare. Some days no new agents would be added but they still get the "cannot resynch on index" error.

I'm planning to change to a different index - one that I will create - rather than using the one created by A5 automatically. It will be the same as the "Agent_nof" index but it won't be defined as "Unique Only". It will be interesting to see what affect this has. I should know the results by the middle of next week.

By the way, for those of you thinking "Normalization", the agent name is included in the main table in order to speed up data entry. They can quickly type the agent name but have no idea what the agent number is; however, I need the agent number for other reasons. Putting both fields in this table was done out of need - not because I simply forgot to use good table design. In the case of this particular application it has even proven useful for checking data accuracy because sometimes the name has been wrong and sometimes the number has been wrong. I wouldn't have been able to find these issues if only one of the fields was stored in the main table. (This is a really fun application.)

[G Gabriel]

Re: "Resynch on index" - v8 experiences?

Cal:
Few issues to consider:
1-Any reason why not let alpha fill the agent's number in the lookup from field rules? I am not a big fan of field rules, but in your scnario it might work better.
2-Does this happen on adding a new agent? or regardless whether it's a new or an existing one?
3-What is the RAM on this computer?

If alpha created the "Agent_Nof" for the autoinc and you are using the same index for lookup, it seems when you add a new agent, alpha will add a new autoincremented number and need to update the index. It will do that upon saving the new record. But sumiltaneously, you are attempting to lookup that very same index, that is being updated! If I were to guess, I would say that is where the problem is coming from. Quite possibly this might happen in two threads, but if alpha is looking up an index that has not been updated yet, you would get the error message. You might either:

1-Disable the script for the lookup when adding a new agent as it serves no purpose there, or
2-To make the lookup take place before updating the index, you might want to put that scipt in CanSave or OnChange instead of in OnSave.

[CALocklin]

Re: "Resynch on index" - v8 experiences?

1-Any reason why not let alpha fill the agent's number in the lookup from field rules?

The lookup is what happens earlier if the user enters a "normal" order. Imported orders and agent records that were modified after the order was originally entered are what this check is really for. This is just an OnSave event that checks to make sure they match and then warns the user if they don't. Of course, since it's in the OnSave event, the check runs whether a different agent has been selected or even if the agent wasn't changed but something else in the order was changed.

From original post: "A lookupc() is being used to make sure the agent name matches the agent number because imported data and modified agent records can cause mis-matches. (No data is changed. Only a message is displayed if there is an issue.)"

2-Does this happen on adding a new agent? or regardless whether it's a new or an existing one?

See item 1. And, no, it doesn't seem to matter whether they have just created a new agent record that's needed because the current order is for an agent who has never placed an order before, selected a different (but not "new") agent, or not even changed the agent. Adding totally "new" agents is actually relatively rare when considered as a percentage of total orders.

3-What is the RAM on this computer?

I haven't checked but they are relatively new computers and the server was updated after this issue started (for other reasons) and the issue continues with the new server. I don't think this should be an issue but it's worth verifying.

The rest doesn't apply since (a) the lookup field rule would occur long before the OnSave event fires and (b) the lookup field rule might not even be used/needed because the current changes to the record might not affect the agent selection at all. The test is actually for cases where the user is changing other parts of the order but NOT the agent info. If the agent name is changed, the lookup handles it correctly and without generating index errors. Of course, the check runs no matter what since it's in the OnSave event.

[G Gabriel]

Re: "Resynch on index" - v8 experiences?

The rest doesn't apply since (a) the lookup field rule would occur long before the OnSave event fires

Not exactly. We are talking about 2 lookup's here

I am refering to the lookup in the script not that of the field rules
When you save a record two things happen:
1-alpha will update the indeces, and
2-the OnSave event will run.
not necessarily in this order.

The event in your OnSave has an expression that employs lookup() which utilizes an index.

As I mentioned earlier, it looks like the Onsave event is firing before alpha is done updating the indeces and hence the error.

[csda1]

Re: "Resynch on index" - v8 experiences?

Cal,

It's not clear as to the cause of your problem, but I'll make a couple of suggestions that might work.

1. open a pointer to the table (probably in the form init event) being used in the lookup prior to the lookupc(). This will make the lookup run faster too. Make sure you cleanup and close the table on form exit

2. Place the lookupc() within a function, and execute that instead.

3. Change the Network card and/or network cable

[CALocklin]

Re: "Resynch on index" - v8 experiences?

When you save a record two things happen:
1-alpha will update the indeces, and
2-the OnSave event will run.
not necessarily in this order.

As stated originally, "This happens in the OnSave Record event when saving work orders. A lookupc() is being used to make sure the agent name matches the agent number because imported data and modified agent records can cause mis-matches."

To make it clearer, the index error occurs when attempting to do a Lookupc() to find something in the Agents table but the record being saved is in the Ord_head table. Saving the Ord_head record does not update the index in Agents and a Lookupc() will not update the index so I don't see the point.

[G Gabriel]

Re: "Resynch on index" - v8 experiences?

but the record being saved is in the Ord_head table.

Is this part of a set that also includes the Agents table?

[CALocklin]

Re: "Resynch on index" - v8 experiences?

1. open a pointer to the table (probably in the form init event) being used in the lookup prior to the lookupc(). This will make the lookup run faster too. Make sure you cleanup and close the table on form exit

This form is often open for hours at a time without ever being closed on up to 4 different workstations all at the same time. It's their main data entry form and almost everything they do on a regular basis starts from this form. Special reports and invoicing is done from other forms but they probably spend 90% of their time in this form. Do you think it would be acceptable to leave that many pointers to one table open for such a long time and with so many other things happening during the time it's open?

If not, the only other thing I can think of would be to try opening the pointer immediately before running the lookup then closing it before the script ends. (The lookup is almost the first thing in the OnSave event.)

2. Place the lookupc() within a function, and execute that instead.

I guess I'm already doing this. The OnSave event actually calls my Ord_Head_OnSave() function and the lookupc() is inside that function. Putting the lookupc() inside another function would seem to be a bit of overkill. (Based on your recommendation I am using far more functions now rather than just using scripts. I can't prove it helps but it hasn't hurt anything either.)

3. Change the Network card and/or network cable

I forgot to mention that they did put new network cards into the 'old' computers. The other two - the server and one workstation were completely replaced. This all happened early last week but the problems continued. I don't believe they've changed any cables but that could be a large undertaking - I'll find out.

I'm going to be sending out an update tomorrow that will use the other index. Assuming I can get them to install it right away, we should know the results by Tuesday. (Unfortunately that is sometimes a big assumption.)

[CALocklin]

Re: "Resynch on index" - v8 experiences?

Is this part of a set that also includes the Agents table?

You had me going for a minute but, no, it's not. The order form is based on a set but the Agent table is not part of that set.

[G Gabriel]

Re: "Resynch on index" - v8 experiences?

Ok Cal..one more idea:
I have always had problems with lookupc("F"..) or "L".
Since you are lookinking unique values, why not use "C" instead?

One other possibility:
You lookup Agents in the field rules. alpha created a temp table based on unique values. Now OnSave, you are looking up the same table, but on a different index. Possibly, the last "query" did not detach. How about detaching all queries, set the index you use for lookup as the base index before running the script?