Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi Guys.

- I have a Wizard that generates a new form at runtime, containing a Grid Control.

- The Wizard passes a reference to one of its temp Tables to the form, in which I use linkPhysicalTableInstance in the runtime form's datasource init() method. - The GridControl has an add new record button, which inserts records in to the tmp table reference.

- The new record is saved to the reference temp table correctly, and displays in the grid *when the runtime form is closed and reopened* but does *not* display in the grid immediately after the insert.

- To add to the weirdness, after a runtime form has been created, a record inserted and then closed, subsequent run time forms do display new record insertions immediately, without needing to be re-opened. Some code snippets below.

Can anyone please suggest why this behavior only happens for the first time that data is inserted in to the temp table, but displays fine for subsequent runs of the runtime form?

Creating the runtime form:

                args                    = new Args(formstr(RunTimeFormName));
                formRun             = classFactory.formRunClass(args);
                formRun             .parmRuntimeFormsGridTmpDS(sysWizard.ReferenceToWizardsTableTmp()); // Passing a reference for Wizards tmpTable to form
                formRun             .init();
                formRun             .run();
                formRun             .wait();
                formRun             .detach();

RunTime Form's parmDataSourceMethod:

public void parmRuntimeFormsGridTmpDS(CommentsGridTmp _ReferenceToWizardsTableTmp)
{
ReferenceToWizardsTableTmp = _ReferenceToWizardsTableTmp;

}

DataSource init() method:

public void init()
{

super();
RuntimeFormsGridTmpDS.linkPhysicalTableInstance(ReferenceToWizardsTableTmp);

}

RunTime Form's New button clicked method:

void clicked()
{
    int64                                             numRows;
    ;
    // Refresh records loaded in grid DS to ensure correct number of records for setting initial index number

// Note: SomeId is passed in Args() record, its passing fine as is the number of rows count - and replacing with a static value has no effect.

select count(RecId) from ReferenceToWizardsTableTmp
where ReferenceToWizardsTableTmp.SomeId == someId;

numRows = ReferenceToWizardsTableTmp.RecId;

    ReferenceToWizardsTableTmp.Comment                  = "Comment " + int642str(numRows + 1);
    ReferenceToWizardsTableTmp.Filename                 = "";
    ReferenceToWizardsTableTmp.someId                    = someId;
   ReferenceToWizardsTableTmp.insert();

    element                                           .Task(#TaskF5);
//    super();
}

So as described above, the first time that the runtime form is created and a record is inserted, it doesn't display. Reopening the form will display the inserted data fine. Also, after reopening the form any new records inserted appear in the grid in real time.

So I guess it must be something to do with the linkToPhysicalTable and where the grid fields look for records to display.

Any help appreciated!

Tim

*This post is locked for comments

All responses (12)

Answers (3)

Brandon Wiese 17,788 on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Glad to hear you got it working, and nice catch. I went through some of my code, and indeed I use a delete_from tmptable statement to initialize the buffer prior to using .linkPhysicalTableInstance(..) myself.

In case you happen to be jumping tiers in your code, here's a small gotcha to watch out for.

https://community.dynamics.com/ax/b/dynamicsaxexperience/archive/2016/01/24/2012-unexpected-degeneration-of-insert-recordset-into-tempdb-buffer
Martin Dráb 230,476 Most Valuable Professional on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

I thought you were passing an existing temporary table, but it seems it wasn't the case, therefore linkPhysicalTableInstance() had nothing to link.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Thanks gentleman - getPhysicalTableName() was a great lead. I took a scattergun approach to printing the table names throughout initialization and operation, and while the two tables do eventually link correctly with the same table name, they take a roundabout way of getting there.

I have a working solution whereby I run a select statement on the buffer reference prior to the linkPhysicalTableInstance operation, which seems to act to initialize the buffer reference despite it being empty. The linkPhysicalTableInstance operation then succeeds at the first run because the buffer properly exists - and changes written to the form DS are now persistent and reflected in the calling Wizard's buffer reference. It is perhaps a bit of a workaround but it works exactly as expected now and is efficient enough. It would be interesting to know why these tables are not initializing as expected when they are empty but I'm all good.

Thanks very much Martin and Brandon, a pleasure to interact with two DAX legends.

Learnings:

- The buffer reference is only acquiring a table name after the runtime form has been closed OR a select statement is run on it before the link.

- The form DS is not acquiring a table name on initialization, it only acquires a table name after being linked to the buffer reference, and then only when the buffer reference has a table name.

TESTING RESULTS

Test 1 - Linking the form DS with buffer reference and inserting in to form DS

1st Run: For the first time of running the runtime form

- Neither tables have a name until the form's close() method. No records are created in either table.

- At the form close method, only the form DS has a table name.

2nd Run: Then when the runtime form is run again (during the calling form's lifetime):

- At startup, the reference buffer from the calling form has a table name but the form DS does not.

- After the super() call in the form DataSource's init() method but before the linkPhysicalTableInstance() call the form datasource still hasn't got a table name.

- After the linkPhysicalTableInstance() in the form DS init() method, the form DS finally has a table name, and it matches the calling form's buffer reference.

This implies that the form DS is only being initialized correctly once linked with the buffer reference, and then only when the buffer reference has been properly initialized. And the buffer reference only appears to be initialized once a record is entered!

My ability to add records to the form DS has gone off a cliff since using the default New Record method with initValue(), new records are just not being registered. So reverting back to inserting in to the buffer reference, I get different results:

Test 2 - Linking the form DS with buffer reference but inserting in to buffer reference

1st Run -

- Again, neither tables have table names at the start.

- After a record has been entered then both the buffer reference and the form DS tables have matching physical table names.

- But only the buffer reference contains the new records (which did not appear in the grid, as the form DS didn't know about them).

2nd Run: When running the runtime form again during the calling Wizard's lifetime:

- Again the buffer reference has a table name, the form DS does not

- After the linkPhysicalTableInstance call, the form DS has a table name, this seems to work because the buffer reference already has a name.

- Once the runtime form is closed, then both the buffer reference and the form DS contain the same records (and the new records appear in the form DS as well this time).

Finally, with/without SELECT Statement to initiliase the empty buffer reference prior to linking with form DS

Test 3 - Linking the form DS with buffer reference and inserting in to form DS WITHOUT the SELECT statement

1st Run & 2nd Run:

- No table names for either source at first, then buffer ref has a name, then the form DS does after linking.

- Changes not persistent.

- Note: info print statement after select should read LINK not POP

Test 4 - Linking the form DS with buffer reference and inserting in to form DS WITH the SELECT statement

1st & 2nd Run:

- As before, neither the form DS or buffer reference has a table name after initializing.

- Running a basic SELECT statement on the buffer reference in the form DS init() method prior to the linkPhysicalTableInstance seems to properly initialize the (empty) buffer and allows the link to be successful.
Brandon Wiese 17,788 on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

By the way, it's sometimes really helpful to be able to inspect the contents of a temp table while debugging, from SQL Server Management Studio using

set transaction isolation level read uncommitted;

select * from tempdb..t107946_BE044A13A9C24283897CA1B59607CBD2;

Which is easy if you have the table name from .getPhysicalTableName(), but even if you don't know it precisely, it's often easy to locate with a little trial and error.

select * from tempdb.sys.tables;

The table will of course start with "t" and the table number. Often it's the most recently created one, so sorting by create_date desc floats it to the top, but of course there can be a pool of them.
Verified answer

Brandon Wiese 17,788 on at

Like (1)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Hi Tim,

I have experienced the problem you are describing. It does seem that using .linkPhysicalTableInstance(..) to change the underlying temp table associated with a form datasource after it has already initialized produces some weird behavior. This seems to be true even when you can demonstrate with .getPhysicalTableName() that they are linked properly.

Here's something you can try, if you haven't yet, that I have found works for me.

Create your form, and call .init(), but not yet .run(). Then use .linkPhysicalTableInstance() to link the freshly created temp table underlying the data source to your external temp table buffer. In other words, instead of trying to transplant your already created temp table into the form's data source, let the form create the temp table, then let the caller transplant that temp table into its own buffer using .linkPhysicalTableInstance(). Then insert records, then call .run(). If necessary, call .executeQuery() on the form data source after .run().

Let us know how it works.

I've blogged a bit about strange temp table behavior in the past. I'd be willing to spend some time trying to reproduce your results in my lab and try various things myself.
Verified answer

Martin Dráb 230,476 Most Valuable Professional on at

Like (1)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

It doesn't merge, replicate nor stacks two tables in any way; they're references to a single table in database.

Your description suggests that your buffers aren't linked correctly, so you'll probably see two different values when calling getPhysicalTableName() on them (which is wrong). If they're linked successfully, you have some other problem why your records are not persisted and that will be the new direction for your debugging effort. For example, you look into database to see whether data is stored there, iterate records at various times to see what happens with them and so on.

If you fail to debug it by yourself, consider preparing a project demonstrating your problem (with everything unrelated removed), so others can run and debug it. Unfortunately I currently don't have any AX 2012 environments in Azure and I won't be at home until Tuesday, so either somebody else would pick it up, or it would have to wait for a few days. But first try it by yourself.

By the way, you can learn from Methods on a Form Data Source which methods you can use when working with records through a datasource.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Ooh I had a ray of hope for a second there - yes, I am initializing some fields so using initValue() appeared to be Occam's Razor. I just implemented that but it has the same effect as inserting records to the RuntimeFormsGridTmpDS - it displays fine but the changes are not persistent in the calling form's buffer (stored in the Wizard), after the runtime form is closed.

The key requirement that is restricting me is that changes made during the runtime form's lifetime should not affect the underlying (non-temp) database - changes must be stored in a buffer of the calling form (a Wizard), until the Wizard reaches finish() (at which point it will iterate through its tempDB table buffer and make changes to the 'real' tables).

As the runtime form's datasource is also of type tempDB, it is empty at runtime and needs to be populated using a reference from the calling Wizard, using the linkPhysicalTable method. Then changes during the runtime form's lifetime should be displayed in the runtime form's grid, but also to persist in the calling Wizard's table buffer.

Once the RuntimeFormsGridTmpDS.linkPhysicalTableInstance(ReferenceToWizardsTableTmp) method is called, should changes to *either* reference be expected to be replicated in the other table? So new records inserted to RuntimeFormsGridTmpDS should also be inserted to ReferenceToWizardsTableTmp? I understand that this doesn't merge the two tables, rather it stacks one on top of the other, as per the job example at the end of blogs.msdn.microsoft.com/.../temporary-tables-ax-2012 - in which case changes to the form DS will not persist in my reference table?

My insert new record method is the "RunTime Form's New button clicked method:" above. Inserting to the calling Wizard's reference buffer is the only way I've found for changes to persist after the runtime form is closed - and its fine except that the 1st record created in the table buffer is not displayed in the runtime form's grid. Closing and reopening the form (while the Wizard is still running) will display the new records correctly, and adding subsequent records will appear in the grid as expected. So it is only when the buffer is empty.

Thanks again for your time Martin.
Martin Dráb 230,476 Most Valuable Professional on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

As I understand, you have a data source in AOT, which gives you two objects at runtime - the table buffer (RuntimeFormsGridTmpDS) and the datasource RuntimeFormsGridTmpDS_ds. And then you created a new buffer for the same table (ReferenceToWizardsTableTmp) instead of using the existing one (RuntimeFormsGridTmpDS) because you had some bug in saving records. If it's the case, let's fix the bug instead of adding more stuff - show us your code for create a new record of the datasource, setting its values and saving it to database.

It would help me if you explained what you doing. For instance, why did you create button for adding record in code instead of using the standard logic offered by AX? If you need to initialize some fields, you can simply override initValue() instead of re-implementing the whole thing.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Thanks again, I understand. There are two datasources in use -

- "RuntimeFormsGridTmpDS" - this is linked to the form as a data source in the AOT. So it's FormDataSource reference is RuntimeFormsGridTmpDS_DS. I tried calling research() on this after making changes to the tmpTable ("ReferenceToWizardsTableTmp", see below) which I pass as a parameter to RuntimeFormsGridTmpDS.linkToPhysicalTable(ReferenceToWizardsTableTmp).

- "ReferenceToWizardsTableTmp" - this is a table declared in the form's declaration (of type TempDB), and is assigned a reference to a table of the same type existing in the calling wizard form, by means of a parm() method called before the form is initialized and run. So this type of table doesn't have a ReferenceToWizardsTableTmp_DS reference as it is not a form data source. Inserting records to this table are persistent.

I am not married to either approach and I'm not 100% sure which approach you are advocating - so I'm interested to know what you think is the correct way (sorry, I know its not your job). Should I persist with inserting in to the "ReferenceToWizardsTableTmp" table reference (which is currently persisting properly, but doesn't display in the grid the first time) OR should I insert in to the form datasource as defined in the AOT form node ("RuntimeFormsGridTmpDS") and then figure out why changes do not persist in the calling form's tmp table (despite running linkPhysicalTable)?

One thing I don't understand please, I am not clear why this behavior only happens when the "ReferenceToWizardsTableTmp" table is empty - if the reference table contains a record then the next time that a record is inserted then the new record is displayed in realtime. So it is only when a record is inserted to the reference table for the first time?
Martin Dráb 230,476 Most Valuable Professional on at

Like (0)

Report

RE: Grid Control linkPhysicalTableInstance to TempTable, first record inserted doesn't appear in Grid, fine afterwards

Is it the right datasource? Shouldn't it be called ReferenceToWizardsTableTmp_ds? (Don't forget that I know nothing about your form except of those few things you mentioned above.)