Some tables use RefRecId and RefTableId fields to build relations with other tables. Hence building data entities for those tables could be not that straight forward exercise. Let’s take WHSFilterTable as an example. We want to build new entity to show customer filter codes. There is already a standard entity, but we will build new one to illustrate the issue. For our entity will join WHSFilterTable and CustTable. Relations between tables:
Entity’s query will be similar to this:
It builds perfectly fine but during DB sync we get huge list of errors. I won’t list all of them here. But main one is “System.Data.SqlClient.SqlException (0x80131904): Invalid column name ‘TABLEID’.“, that happens during trigger creation. Sync engine generates SQL statement that has TableId field in it, but TableId is not a real field and does not exist in underlying tables.
So what do we do? Because we need that entity and it’s quite common to use RefTableId, so error is not specific to WHSFilterTable.
If you look at SQL views generated for entities you could notice that quite a few of them do not have triggers at all. Triggers are generated only for entities that support set base operations, change “Enable Set Based SQL Operations” property to “No”, no triggers will be generated and entity will be synchronized without any issues.
Do not use TableId in the relation
Instead of having relation by tableId we can remove it and add range over RefTableId field. Value for this range could be SysQueryRangeUtil method, that simply returns tableNum(CustTable). Value is calculated only once during DB sync and then embedded into the view definition. And that’s what you can see in the standard WHSCustomerProductFilterEntity entity. It uses WHSCustomerProductFilterEntityHelper::getCustomers() method to resolve this particular issue in the standard product.
You can use DbSyncEnableTableIdInDataEntityTriggerRelations flighting
INSERT INTO SYSFLIGHTING (FLIGHTNAME, ENABLED, FLIGHTSERVICEID) VALUES (‘DbSyncEnableTableIdInDataEntityTriggerRelations’, 1, 12719367))
It forces sync to use actual values instead of table ids during triggers generation and looks like it was introduced to deal with this particular issue. I have no idea why it’s not documented anywhere and not enabled by default.
While we are waiting for Microsoft-hosted agents support (build without a VM) to be released, we have to have a dedicated build VM that generally sits there and does nothing. To save costs we can setup a schedule to turn it on and off, however, you have to align your build schedule with it, and it does not make a lot of sense if you do not have active development and do changes once in a while.
Here is a quick and dirty workaround. You can get Azure Virtual Machine Manager extension from visual studio marketplace.
It adds one task to stop or start a VM.
Now we need to modify standard build definition and add new task to start build VM.
You cannot add it to existing agent job, because this one is self-hosted and requires VM to be up and running, catch 22! So, add another agent job:
Bring it forward and add Azure VM task:
Now your pipeline should look like this:
And to make it work we need one more change. In the build options it demands DynamicsSDK and obviously new agent won’t have it and will fail, so I simply removed DynamicsSDK from the list, that’s why I call this quick and dirty!
To stop a VM after the build I put Azure VM task in the very beginning of the release pipeline that is automatically triggered after a successful build.
Using this neat extension, we can automatically start a VM before a build starts and then immediately turn it off after the build. I deliberately put stop task in the release pipeline, so it won’t stop VM if build fails and I can investigate or quickly rerun it without waiting for VM startup. Obviously, one day we will get ability to use MS hosted agent but meanwhile it may help you to save some $$.
If you want to enable Power BI Embedded for your OneBox (Tier 1) environment follow this outstanding guide . However, It may not work for you and you can get “Failed to publish Power BI Embedded report” or “LoadReportFailed” errors. One of the possible reasons (if you followed the guide precisely and did not mess with the setup) is deprecation of Workspace Collections. The good news is that there is a work around. Azure subscription with workspace collection should be connected to LCS implementation project and that’s it! Please note that it’s not necessary to have FnO VM or Azure SQL Server in the same subscription with workspace collection, so workspace collection can be deployed to implementation project subscription and VM with DB can sit in your demo subscription, even in different Azure region.
In previous 4 parts I showed how to build new format without a line of X++ code, but unfortunately to run it you must go to ER workspace. However, it is possible to run it from X++ as well, code below could be easily invoked from a new button to give users better experience.
var runner = ERObjectsFactory::createMappingDestinationRunByImportFormatMappingId(_mappingId, _integrationPoint);
runner.withParameter(inputParameters); //optional, to pass parameters if required
MappingId is import format configuration. Usually, you have a parameter where a user can select configuration, for example, import format configuration field on bank statement format table.
IntegrationPoint is a string value that you populate on the model mapping designer screen:
Usually, format is classStr(ERTableDestination) + ‘#’ + tableStr(TargetTable)
InputParameters is an instance of ERModelDefinitionDatabaseContext class, where you can add parameter name and value pairs:
ERmodelDefinitionInputParametersAction inputParameters = new ERmodelDefinitionInputParametersAction();
inputParameters.addParameter('$ExecutionID', _executionID).addParameter('$AccountId', bankAccount);
In the previous blog post series we learned how to import simple CSV file. However, CSV and other text files may contain records of different types that should be imported to different tables or process differently. In this post we will enhance format and mapping created. Let’s say in our example first column represents records type (a, b or c):
In this case we need to change format and add new “CASE” element:
And 3 record sequences instead of one:
Note that each sequence has a predefined value for the String field, that’s how we tell ER that if record has “a” in the first column it should be parsed with RecordA sequence. Also, we changed Multiplicity to “One many” for the sequences, to tell ER that there is at least 1 record for each type. It could be set to “Zero many” if a record is optional or “Exactly one” if we expect it only once in a file.
Now we need to change mapping. Each sequence has system field “isMatched” that is populated if file record is matched to sequence pattern. We will use it to bind 3 record types to same model field, but in a real life examples different records may go to different tables, like header and lines.
Expression used is pretty simple, it takes value from the first record if it is mapped, if not, it checks second record and if it is not mapped as well it takes value from the third.
IF(@.Types.RecordA.IsMatched, @.Types.RecordA.Data.FieldInt, IF(@.Types.RecordB.IsMatched, @.Types.RecordB.Data.FieldInt, @.Types.RecordC.Data.FieldInt))
Also using IsMatched you can provide default values when it is missing in a file.
That’s the last step. We have Format to parse data to a model, now we need to write data from model back to actual tables. Open model designer and click “Map model to datasource” in the action pane.
Create new record, set name and description. Select direction, for import it should be “To destination”:
Open designer. In the Data Sources area add data model:
Set name, select model and definition:
Add new destination:
Select table you want to insert to, set name, specify if it should support insert or update\insert and if you need validation to be triggered:
Bind Record List from model to tables’ record list and bind field accordingly:
Save and we are ready to test it!
Go back to Model to Datasource mapping screen, select mapping record and click run in the action pane:
Select file and check the result. I do not have UI for this table, so just going to use table browser:
It’s not that nice to trigger import from the mapping record but that’s the only way I know and in the next post we will look how to trigger it from X++ code, so we could have a button somewhere.
As you can see ER is a really powerful tool that allows users to do tasks that were not possible without development before.
In this blog post we will create new mapping to map format to model. On the format designer screen, click “Map format to model” button. Create new record, select model definition, specify name and description:
Open designer. In the designer bind Record List from the format to Record List in the model and then bind fields accordingly.
Finally, we can test our format. For the test I’m going to use simple CSV file:
Go back to Model to Datasource mapping form, click run in the action pane and upload the file.
If we’ve done everything right, we will get XML file that contains data mapped to model or errors, if any:
At this stage we have Data Model, Format and Mapping that we’ve tested. In the next blog post we will do the last piece of the setup – map Model to Destination and test the whole import.