Table data insert or update problem

stony

Hello,
we make our production planning in a homemade from tailored to us.
Process Description: All full hours the data (so desired!) are recalculated and written to a table. This table is then the basis of various pages and reports. This works fine.
Only now I have the problem that this computation may take a 3 minutes. And as long as the data in this table are of course not yet complete.

One idea would be to create a further table and to fill with the first table, when the computing process would be completed, and to take as a basis for the pages and reports. This way I should be able to reduce the time. But no I can not exclude it either.

How could I solve this.

einsTeIn.NET

I'm not sure if I understand your issue right. But from my point of view there are several options to solve it.

• Use another table (same definition) to create your record and TRANSFERFIELDS (or something similar) in the end
• Use a temporary record variable to create your record and copy it afterwards to your real table
• Create some kind of flag field in your table to identify records that have been inserted but not completely processed
• Try to change your solution in the way to insert the record at the very end when everything has been completely processed

stony

The problem is that the table is used only when all records have been inserted.
Can I disable access to that table as long as the calculation runs.

Troubles_In_Paradise

stony wrote:

The problem is that the table is used only when all records have been inserted.
Can I disable access to that table as long as the calculation runs.

How many Pages/Reports use this table?
I ask this because I was thinking to a SingleIstance Codeunit that switch on and off acess to that table.
I explain better:

1. Start Calculation
2. Call a function SingleIstanceCodeunit.DisableAcess

DisableAccess defined in Single Instance codeunit has the following code
Access := false

3. End Calculation
4. Call a function SingleIstanceCodeunit.ActivateAccess

ActivateAccess defined in Single Instance codeunit has the following code
Access := true

in a report/page that try to access this page I'll do the following:

OnOpenPage()
if Not(SingleIstanceCodeunit.AccessGranted) then
  error('Wait for Calculation End');

AccessGranted defined in Single Instance codeunit has the following code
Exit(Access)

Something like that...
what do you think?

David_Singleton

Add a semaphore flag in the header (I assume you have some kind of header related to the Planning lines). This can be a Boolean or option depending on your needs, but an option gives you better flexibility. (you might have options like Consistent,Locked,Updating,Corrupt)

When you start the process lock the header, read the status of the flag if it is OK then change the flag modify and commit. Then run the routine, on completion undo the change with the reverse sequence.

Then before running any reports make sure first that the flag is consistent. Play with the logic of where you change the flag status in relation to your commits, and if the process crashes the status should be corrupt, so you need to manually fix it.

David_Singleton

Also keep in mind that locktable doesn't really lock the table, it just stops other sessions from modifying the locked records.

David_Singleton

Troubles In Paradise wrote:

...
How many Pages/Reports use this table?
I ask this because I was thinking to a SingleIstance Codeunit that switch on and off acess to that table.
...

:?: I can't see how that will work. How can a SingleInstance code unit switch off access to a table? DO you mean by using some ADO or similar to directly access the SQL database directly?

Troubles_In_Paradise

David Singleton wrote:

Troubles In Paradise wrote:

...
How many Pages/Reports use this table?
I ask this because I was thinking to a SingleIstance Codeunit that switch on and off acess to that table.
...

:?: I can't see how that will work. How can a SingleInstance code unit switch off access to a table? DO you mean by using some ADO or similar to directly access the SQL database directly?

No I don't.

Help About "SingleIstance"
When you set this property to Yes on a codeunit, all codeunit variables that use this codeunit use the same instance. That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. The codeunit remains instantiated until you close the company.

The following example shows how you can use the SingleInstance property.

Two forms can connect to the same codeunit.

On Form1:

Codeunit1.SetNumber(100); 

On Form2:

Number := Codeunit1.GetNumber(); 

 MESSAGE(Format(Number)); 

The SingleInstance property in Codeunit1 is set to Yes. Form1 calls a function on Codeunit1 and sets the parameter to 100. Codeunit1 saves this parameter in a local variable. Form2 is now able to get the parameter value (=100) from Codeunit1. A message is displayed.

Because of this I suggested to use it as "TrafficLight" in order to understand if calculation is ended.
Is it clear?

stony

Currently there are 4 pages and 5 reports that use this table. I think your proposal well and will give it a try.
thanks

David_Singleton

Troubles In Paradise wrote:

Because of this I suggested to use it as "TrafficLight" in order to understand if calculation is ended.
Is it clear?

Sorry, the concept seems interesting, but I just don't understand how it will work. I must be missing something. :-k

David_Singleton

Troubles In Paradise wrote:

Help About "SingleIstance"
When you set this property to Yes on a codeunit, all codeunit variables that use this codeunit use the same instance. That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. The codeunit remains instantiated until you close the company.

The following example shows how you can use the SingleInstance property.

Two forms can connect to the same codeunit.

On Form1:

Codeunit1.SetNumber(100); 

On Form2:

Number := Codeunit1.GetNumber(); 

 MESSAGE(Format(Number)); 

The SingleInstance property in Codeunit1 is set to Yes. Form1 calls a function on Codeunit1 and sets the parameter to 100. Codeunit1 saves this parameter in a local variable. Form2 is now able to get the parameter value (=100) from Codeunit1. A message is displayed.

Hmm I wonder if you actually read this, or just copy pasted from online help :-#

Troubles_In_Paradise

David Singleton wrote:

Troubles In Paradise wrote:

Help About "SingleIstance"
When you set this property to Yes on a codeunit, all codeunit variables that use this codeunit use the same instance. That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. The codeunit remains instantiated until you close the company.

The following example shows how you can use the SingleInstance property.

Two forms can connect to the same codeunit.

On Form1:

Codeunit1.SetNumber(100); 

On Form2:

Number := Codeunit1.GetNumber(); 

 MESSAGE(Format(Number)); 

The SingleInstance property in Codeunit1 is set to Yes. Form1 calls a function on Codeunit1 and sets the parameter to 100. Codeunit1 saves this parameter in a local variable. Form2 is now able to get the parameter value (=100) from Codeunit1. A message is displayed.

Hmm I wonder if you actually read this, or just copy pasted from online help :-#

I read it what I'm doing wrong?

David_Singleton

Troubles In Paradise wrote:

That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. 

I read it what I'm doing wrong?

Maybe you didn't realize that a single instance code unit is PER SESSION. So if there are 100 users, then only the one user running the update will see this, the other 99 can just do what they want. And the person running the update in any case can't run a report at the same time, so it wont even help them.

Or the bit I didn't understand, is that maybe you have a way of "sharing" this semaphore across multiple clients, which is why I was interested. If you have than I would really like to know how. Otherwise I would have just ignored the post.

Troubles_In_Paradise

David Singleton wrote:

Troubles In Paradise wrote:

That is, all codeunit variables of this codeunit use the same set of internal variables when the code is running on the same client. 

I read it what I'm doing wrong?

Maybe you didn't realize that a single instance code unit is PER SESSION. So if there are 100 users, then only the one user running the update will see this, the other 99 can just do what they want. And the person running the update in any case can't run a report at the same time, so it wont even help them.

Or the bit I didn't understand, is that maybe you have a way of "sharing" this semaphore across multiple clients, which is why I was interested. If you have than I would really like to know how. Otherwise I would have just ignored the post.

I have no way to share the TrafficLights, it's clear.. works only for the current session.
But I think it's easy to accomplish this using a field in a table using the same logic and in this way all users can read this field.

David_Singleton

Troubles In Paradise wrote:

I have no way to share the TrafficLights, it's clear.. works only for the current session.
But I think it's easy to accomplish this using a field in a table using the same logic and in this way all users can read this field.

Pity then. I was hoping you had found some neat trick to do this. Adding a separate field (like I suggested above) is the way I always do it, because I don't like taking things like this outside of Navision. The issue though is always what happens when the session crashes or you lose connectivity etc. That's why I was hoping you had solved the issue with a single instance codeunit that would die when a session crashes.

Troubles_In_Paradise

David Singleton wrote:

Troubles In Paradise wrote:

I have no way to share the TrafficLights, it's clear.. works only for the current session.
But I think it's easy to accomplish this using a field in a table using the same logic and in this way all users can read this field.

Pity then. I was hoping you had found some neat trick to do this. Adding a separate field (like I suggested above) is the way I always do it, because I don't like taking things like this outside of Navision. The issue though is always what happens when the session crashes or you lose connectivity etc. That's why I was hoping you had solved the issue with a single instance codeunit that would die when a session crashes.

I'm sorry for the misunderstanding...

David_Singleton

Troubles In Paradise wrote:

I'm sorry for the misunderstanding...

Why? Learning is good. :thumbsup: