How to write from Temp table to persistent table

Kenmu · 2018-10-25

I have created a temp. table and read data from the jobqueues of multiple companies into it in order to gain easy access to our jobqueues. My issue is that, if fx. a job should be set to "ready" after failing, I want to be able to set the job to "ready" from the page loaded from the temp table.
However, since the ID-GUIDs are no more unique than appearing as ID's in the various companies, I have had to CLEAR the GUIDs and create new ones for the temp table in order to get all the data with unique IDs. The problem about that is of course that I have now lost the original GUID and therefore the reference for the entry in the original table, and thus cannot perform any actions on the original table.

Is the a neat solution to this that I'm missing?

Thanks

I have this code to read data into the temp table:

IF Company.FINDSET THEN REPEAT
IF jobqueueEntry.CHANGECOMPANY(Company.Name) THEN BEGIN
IF jobqueueEntry.FINDSET THEN
REPEAT
Rec := jobqueueEntry;
CLEAR(Rec.ID);
Rec.ID := CREATEGUID;
Rec.Text1 := Company.Name;
Rec.INSERT;
UNTIL jobqueueEntry.NEXT = 0;
END;
UNTIL Company.NEXT = 0;

Slawek_Guzek · 2018-10-26

One of possible solutions, witout a need of modifying any table:

This is your temp buffer:

IF Company.FINDSET THEN REPEAT
  jobqueueEntry.CHANGECOMPANY(Company.Name);
  IF jobqueueEntry.FINDSET THEN
    REPEAT
      TempTable823.ID +=1;
      TempTable823.Name := Company.Name;
      TempTable823.Value := FORMAT(jobqueueEntry.ID);
      TempTable823.INSERT;
  UNTIL jobqueueEntry.NEXT = 0;
UNTIL Company.NEXT = 0;

The page displaying the Job Queue entries across companies is based on table 823, and has a global JobQueueEntry record. Fields from global JobQueueEntry var can be displayed on the page.

You run this page by calling PAGE.RUN(yourpageID, TempTable823)

Then in on OnAferGetRecord (or in OnAferGetCurrentRecord if the page is Card) you can do:

JobQueueEntry.CHANGECOMPANY(Name);
EVALUATE(JobQueueEntry.ID, Value);
JobQueueEntry.FIND;

Then your Actions can use current value of global JobQueueEntry rec.

Since the table used to buffer JobQueueEntry PKs per company is a simple one it will not allow you to filter for any specific values from any field from Job Queue Entry table.

Slawek_Guzek · 2018-10-29

I would normally build a function, the first part of it would be the code populating temp table based buffer, and righ aftter that a call to PAGE.RUN (or PAGE.RUNMODAL)

PROCEDURE CheckJobQueues()
BEGIN
  IF Company.FINDSET THEN REPEAT
  //..
  UNTIL Company.NEXT = 0;
  PAGE.RUNMODAL(YourPageID, TempTable823);
END

I woud then call the function from an Action. If the function was meant to be used form more than one place I would put it in a separate codeunit. If you have a spare codeunit to be used just for that you can stick all the code in OnRun() trigger, and then you could link it to the menu, etc.

Juhl · 2018-10-25

Make company a part of PK in your table. You would need that anyway to know witch company to handle job in anyway!

wait · 2018-10-25

Hi, I find it very strange if you are getting the same guid for more than one record, it should not be possible. Is it possible you need to clean some stuff out of your temp table when running the code? If you really have records with the same guid could you store the original guid in another field?

Juhl · 2018-10-25

Standard jobs in NAV and copy company will give you identical guides.

Kenmu · 2018-10-26

@Juhl I am not allowed to make changes to our tables as things are at the moment, unfortunately.

@wait I guess I could save the original guid in some other fields. A guess key:value would be optimal but that doesn't seem to be an option with c/al. Id rather not hardcode 15 variables. Do you have an idea for this?

Juhl · 2018-10-26

If you have a datastructure that requires a PK of Company and GUID, and you cant change the table to fit the datastructure, then its hard to fix.

Slawek_Guzek · 2018-10-26

One of possible solutions, witout a need of modifying any table:

This is your temp buffer:

IF Company.FINDSET THEN REPEAT
  jobqueueEntry.CHANGECOMPANY(Company.Name);
  IF jobqueueEntry.FINDSET THEN
    REPEAT
      TempTable823.ID +=1;
      TempTable823.Name := Company.Name;
      TempTable823.Value := FORMAT(jobqueueEntry.ID);
      TempTable823.INSERT;
  UNTIL jobqueueEntry.NEXT = 0;
UNTIL Company.NEXT = 0;

The page displaying the Job Queue entries across companies is based on table 823, and has a global JobQueueEntry record. Fields from global JobQueueEntry var can be displayed on the page.

You run this page by calling PAGE.RUN(yourpageID, TempTable823)

Then in on OnAferGetRecord (or in OnAferGetCurrentRecord if the page is Card) you can do:

JobQueueEntry.CHANGECOMPANY(Name);
EVALUATE(JobQueueEntry.ID, Value);
JobQueueEntry.FIND;

Then your Actions can use current value of global JobQueueEntry rec.

Since the table used to buffer JobQueueEntry PKs per company is a simple one it will not allow you to filter for any specific values from any field from Job Queue Entry table.

Kenmu · 2018-10-29

@Slawek_Guzek
Neat. Thanks.

One question, from where would I call PAGE.RUN(...)?

Slawek_Guzek · 2018-10-29

I would normally build a function, the first part of it would be the code populating temp table based buffer, and righ aftter that a call to PAGE.RUN (or PAGE.RUNMODAL)

PROCEDURE CheckJobQueues()
BEGIN
  IF Company.FINDSET THEN REPEAT
  //..
  UNTIL Company.NEXT = 0;
  PAGE.RUNMODAL(YourPageID, TempTable823);
END

I woud then call the function from an Action. If the function was meant to be used form more than one place I would put it in a separate codeunit. If you have a spare codeunit to be used just for that you can stick all the code in OnRun() trigger, and then you could link it to the menu, etc.

kriki · 2018-10-30

[Topic moved from 'General Chat' forum to 'NAV Three Tier' forum]

How to write from Temp table to persistent table

Best Answers

Answers

Categories