Power Automate Function - This operation on table '' may not work if it has more than 1000 rows.

 During the creation of my first function to be used with Power Automate I received this warning when attempting to use the 'Filter()' function.  This was odd as I know this function IS delegable (The warning is saying it will become NON-delegable).  The warning ended up being correct as my flow failed because it was trying to filter against a table with more than 1000 rows.

With some trial and error I found two comparisons in my filter that were causing this to occur:

1. Comparing a Yes/No choice field to true/false
    

    myChoiceField = true

This will cause the filter to become non-delegable.  In this cause you need to get the actual Yes/No value from your field.  It needs to look like this:

    myChoiceField ='My Choice Field (My Table)'.Yes

2. Using a 'Grandparent' lookup field (A lookup field to a lookup field)

In my table I have lookup field to a Header/Parent table which then does a lookup field to another table.

    'Initial Lookup'.'Next Lookup'.GUIDField = GUID(GUIDValue)

To solve this it is easiest to create a GUID field on the table you are using in your 'Filter' with a formula:

    Text('Initial Lookup'.'NextLookup'.GUIDField)

This will give you the GUID in a text field on your table. You can then make the comparison and your 'Filter' stays

delegable!

D365 F&O Dual-Write Error: Couldn't resolve the guid for field: teamid.businessUnit

 After refreshing a Tier 2 from another Tier 2 and attempting Dual-Write, We received the following error:

Couldn't resolve the guid for the field: teamid.businessUnit. The lookup value was not found: [BusinessUnit Name]. Lookup parententity: teams, lookup result: .. Try this URL(s) to check if the reference data exists: https://operations-[envrionment].crm.dynamics.com/api/data/v9.0/teams?$select=name,teamid&$filter=… eq '[guid]'

Turns out the solution is very simple.  Go into Power Platform Admin Center, Select 'Manage', then select your environment.  On that page select 'Business Units'. Find you business unit, select it, and then hit 'Edit'.

Simply change the name by adding a single character at the end, save it, then remove the character you added.

That's it!

AX 2012 - Failed to find workflow

 We recently had an issue where our workflow batch job would fail with this error.  Other workflows would be partially run but not fully due to the failure.  For example, if you approved a workflow it would show the approval in the 'Workflow history' but it wouldn't advance the workflow.  It would just sit in review.

The solution turned out to be pretty easy. I found this blog post. 

Run this query to get a RecId of the corrupted record:

Select a.RECID 
FROM SYSWORKFLOWMESSAGETABLE a 
WHERE a.ROOTCORRELATIONID 
NOT IN(Select b.ROOTCORRELATIONID from SYSWORKFLOWTABLE b) 
and a.MESSAGELIFECYCLESTATE = 1

Run this query to update the record found:

Update SYSWORKFLOWMESSAGETABLE 
Set MESSAGELIFECYCLESTATE = 2 
where RECID = [RecId from above]

That's it!

D365 F&O - No Certificate Found for id

 Recently I created a new Tier 1 environment and refreshed the DB from another Tier 1 environment. After attempting to edit a Vendor, we received the following error:

Encryption error occured with exception: Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionException: Encryption error occured with exception: Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'xxx'.

This is being caused by the previous environment's connection to dual-write.  Whereas my new Tier 1 doesn't have (nor need) dual-write, the solution to this is the following:

Using SSMS delete all records in the following tables:

DUALWRITEPROJECTFIELDCONFIGURATION
DUALWRITEPROJECTCONFIGURATION

This will prevent the the environment from attempting to use Dual-Write.

D365 F&O error opening project in VS 2022 - The imported project was not found

 I recently switched to a new Tier 1 machine and VS 2022 for my F&O development. After pulling down all items in version control I received the following error:

The imported project "C:\Program Files (x86)\MSBuild\Microsoft\Dynamics\AX\Microsoft.Dynamics.Framework.Tools.BuildTasks.targets" was not found.

The fix for this was relatively easy.  Open the project file in Notepad++ (Or another text editor) and change this line:

<Import Project="$(BuildTasksDirectory)\Microsoft.Dynamics.Framework.Tools.BuildTasks.targets" />

To this:

<Import Project="$(BuildTasksDirectory)\Microsoft.Dynamics.Framework.Tools.BuildTasks.17.0.targets" />

D365 F&O Tier 1 Update Failure Step 25 AOSService

When upgrading a tier 1 environment you could receive the following error:

GlobalUpdate script for service model: AOSService on machine

This usually means an issue with the DB sync, however, the log zip file doesn't always contain a file indicating the error.  Here are some steps to fix this issue:

1. Ensure VS 2022 is fully upgraded.
1. If you had to upgrade VS 2022, uninstall D365 F&O extension
2. Re-install extension from K:\DeployablePackages\                                    [ID]\DevToolsService\Scripts\Microsoft.Dynamics.Framework.Tools.Installer.17.0
2. Full compile of ALL modules
3. DB Sync
4. Restart environment
5. 'Resume' upgrade

D365 F&O Service Update failure DevToolsService

Recently when upgrading tier 1, cloud hosted, environments from 10.0.41 to 10.0.42,43, or 44 I received the following error:
 
Update script for service model: DevToolsService on machine

On some of my environments I would receive an error indicating it couldn't find VS 2022 and others that had VS 2022 it was another error.  

In order to get past this I would do the following:

1. If missing VS 2022, install it.
1. If it is already installed, update it.
2. Remove the D365 F&O extension if it is installed.
2. Install D365 F&O extension from K:\DeployablePackages\[ID]\DevToolsService\Scripts\Microsoft.Dynamics.Framework.Tools.Installer.17.0
3. In the same 'Scripts' folder, run the powershell command '.\AutoUpdateDevToolsService.ps1'
4. If that is successfull, copy and make a duplicate of that command.
5. Delete all code in the original .ps1 file and then resume your service update.

Vendor record creation DirPartyTable lookup / creation by name.

 We have a custom vendor creation form but needed it to behave similarly to the standard VendTable form when creating a new vendor and selecting or creating a party.  In order to do this you need to join 'DirPartyTable' to your master record table as datasources in the form, add the 'DirPartyTable' name field as a control and then add the following code.

At the form level, add this variable:

On 'init' of the form, initialize the variable and setup the parms:

On 'createRecord' of the form:

I comment out super as my form is for creation only.

And finally, you will need this parm method in order for the selected 'DirPartyTable' record to come back to your datasource:

That's it!  You can now use the full functionality of looking up 'DirPartyTable' records by name.

D365 F&O Email distributor batch add CC

 Well, it has been a while since my last post... So here go...

I recently had the requirement to have CC ability with our emails that I am created via code. We, however, use the SysOutgoingEmailTable and Email templates to send our emails.  The SysOutgoingEmail has no CC field.

Here is my solution.

Create a new extension and add a new field (I created an EDT that extends SysEmailRecipients) to the SysOutgoingEmailTable.

Create extension of SysEmailDistributor class and create chain of command call on initializeMessageBuilder method.

In this chain of command, use the messageBuilder object to 'addCc'.  I also break my field up by ';' because I will have, potentially, multiple email addresses.

Lastly, you need to create your own class and batch job to run for this:

I'm not sure why, but the chain of command isn't enough as the standard SysEmailDistributor won't pick it up. Once you create your own class and a batch jobs, you are all set.

You then simply need to populate your custom field with the CC addresses when creating your email in your code.

That's it!

D365 F&O - When posting Service Orders, Project Item Journals, Item Transfer Journals, A key with the name 0.00 already exists.

 Recently we ran into an issue when posting Service orders, project item journals, or item transfer journals with multiple lines.  The error we received is:

Check - Journal Journal: 000341 A key with the name 0.00 already exists.

In looking deeper, it seemed that the issue was a violation of the primary key on the InventJournalTrans table that is associated with the voucher.  The actual error is occuring in classes\JournalTransList\selectDB class\method.  Inside that class the 'add' method is called and when calling 'ins' on a RecordInsertList it is failing.

In doing some searching I found a post for AX 2009 here.  This post is nearly 15 years old!  However, my situation is the exact same.  I have an extension of the InventJournalTrans where I added the SMAServiceOrderLineNum field which extends the 'LineNum' EDT.  This was causing the system to think my custom field was part of the index and therefore throwing the error on multiple lines.

Solution:

As mentioned in the link, create your own Real EDT that extends 'LineNum' and use that for your field on the table.

Power Automate Looks like your flow's connection needs to be signed-in again

 Yesterday one of my Power Automate cloud flows started fail with the following message:

Looks like your flow's myemail@mywork.com connection needs to be signed-in again. The most common cause is a changed password or a policy set by your tenant administrator. Connections may also require reauthentication, if multi-factor authentication has been recently enabled for your account.

I didn't have a password change nor did I enable MFA.  It could have been a policy change in our tenant, however, my infrastructure team hasn't been able to confirm.  

I was able to fix the issue by logging into make.powerautomate.com in a Chrome incognito window.  Once I logged in I was showin this:

These are the 3 cloud flows that I have created.  Once I hit 'Reauthenticate' I was then taken to:

I clicked 'Fix connection' on each item and completed a short logon procedure.  This fixed the issue.

This blog post was helpful in understanding what possible causes were and the solution.

Dataverse web api 'Expand'

Today, I was attempting to use the 'Expand' feature in the web api to help me with a lookup field on my custom dataverse table.  No matter what I did, I could get it to work.  It kept telling me that my property 'ver_dataareaid' didn't exist on my table.  It, obviously, did so I knew I was doing something wrong.  When using the 'Expand' you need to use the Schema name of the field:

As you can see, the 'Schema name' can be found under the 'Advanced options' when you 'Edit' the column.  This gave me the actual 'Name' value of the column rather than a guid from the lookup.

Edit Dataverse table record error: It is not possible to change transferred line.

I have a dataverse table that was populated using Dual-Write and a D365 F&O entity.  When attempting to edit a record directly on the dataverse table, I get the following error: 

There was an error in saving this record. Unable to write to Finance and Operations apps due to following error(s): {"Write failed for entity VER_SMAServiceOrderLineEntity with unknown exception - It is not possible to change transferred line.\nvalidateWrite failed on data source 'SMAServiceOrderLine (SMAServiceOrderLine)'"} Please rectify your data and try again. If issue persists after multiple retries, please contact your system administrator.

This error is coming from the 'validateWrite' method on the SMAServiceOrderLine table because the service order is in a 'Posted' status.

 

D365 F&O - DMF Import 'Field 'Service Order' does not allow editing'

 I came across this error while trying to import data into a copied version of the 'SMAServiceOrderHeaderEntity'.  I posted a question about here and was able to find the following solution.

I have a custom Data Entity for the Service Order Table.  My primary key is my ServiceOrderNumber field.

When I import a file with an existing ServiceOrderNumber it updates the record as expected.

However, if I want it to create a new record I can't use the 'Auto Generate' on my primary key as that would break the 'update'.

So, I decided to follow what had been done in the CustCustomerV3Entity.

I set my 'ServiceOrderNumber' 'Allow Edit' field properties on the Entity and Staging table to 'Yes'.

In looking through all the setup and code for the customer entity, this seems to be it.  However, I still get:

Originally, I thought the second error was tied to the first, however, it was not.

In doing more research, I found that the label from this error was this:

 

 

This label is referenced in 4 classes. Only 1 of which would be used by DMF.  

 

That led me to the NumberSequenceTable and the 'numAllowEdit' method.

That method says that unless one of 3 fields is set to 'Yes', the field using the number sequence is NOT editable:

 

 

Once I set 'To a Higher Number' to yes (Seemed like the safest change for my purposes) the '... does not allow editing.' error went away.

The other error remains.  It looks like it isn't generating a new Service Order Number like it should.  Based on the CustCustomerV3Entity, I did the following in the 'initvalue' method of my data entity:

 

 

This is similar to what I see on the CustCustomerV3Entity and other Data Entities that allow Write operations.

I believe this is supposed to tell the field to use the Number Sequence specified in 'SMAParameters'.

However this did not work.  After some trial and error, I found that I had to remove the 'SERVICEORDERNUMBER' column (In my case XML attribute) from my file completely.  It then would successfully import.

This may cause an issue down the line as my import file could come with that field defined for an update or not defined for a new record creation.  We will see how that works out.

D365 F&O - Workspace form pattern error: The browser session was lost due to an error on the server.

This week I was attempting to prototype a workspace in F&O.  After walking through the 'Pattern' I applied to the newly created form, I ran my workspace and ran into this error:

It doing some testing I found that once I got to the 'Form Part' of the 'Tab Page' of the pattern, I had to specify a menu item for the form part. This, basically, shows another form inside your workspace form.  Once I specified that, it worked great.

AX 2012 R2 CU9 (Originally CU6) to D365 F&O Code & Data Upgrade

 Over the past several weeks I have been attempting to upgrade our AX DB into D365 mainly using instructions from here.  

Before doing the steps there, you will need to see if you  have a 'Display' menu item named 'SysCheckList_UpgradeToNextV'.  If you don't have that (Which I did not), then you will need to install KB4048614.  This will give you an upgrade checklist to prep your database and modelstore for the upgrade. (This will require going through the standard software update checklist.)

After going through some failed attempts, I narrowed my focus to 2 main paths:

1. Upgrade my company's custom code / data ONLY.
1. This means removing ALL ISV models. This was ok for us as our ISVs have D365 solutions.
2. Upgrade my company's custom data that fits in vanilla AX. 
1. This means removing ALL custom models.

For BOTH options, you must do a full compile, full CIL, and FULL DB sync along with running the new checklist from above.

For option 1, the best idea is to move ALL your customizations into 1 single model.  For some reason, mine were spread out over 4 different models.  You can keep it in different models, however, I think it makes it more difficult.
After attempting a code and data upgrade for option1, we decided to remove ALL non data related customizations. (Classes, forms, etc.) This would leave us with only our Tables, EDTs, etc. 

However, after looking at the amount of work to upgrade this, we decided to give option 2, from above, a try.

All customizations were removed and we began the 'Data upgrade' using the link above.  Our database is pretty big (Over 300 GB). This upgrade process took over 48 hours.  Aside from time, we only ran into a couple of errors in the 'ExecuteScripts' step.  We had to skip 2 steps. To do that, you can follow instructions here. For some reason there were duplicate records in 4 tables so there were some DB sync failures.  They were easily fixed and we were able to get the full data upgrade completed.

AX 2012 R2 CU9 DIXF Data Export

 As we are now moving to D365, we need to export our data. Our partner assumed we had DIXF, however, we are in CU6 and it doesn't have DIXF.

In order to use DIXF we upgrade one non-production environment to CU9. (Microsoft recommended either CU8 or CU9 not CU7).

Once we upgraded that, we could then use DIXF.  However, this left an issue of how to get new data from PRODUCTION into this CU9 environment.

1. Using the test data transfer tool, export out ALL DMF tables.
2. Refresh non-production environment transactional DB as usual. (DO NOT refresh model DB)
3. Run CU9 KB upgrade to upgrade the DB
4. Run and complete the software update checklist.
5. Using the test data transfer tool, import ALL DMF tables.

Although this does take me about 3 hours to complete it gets the job done.  Steps 1 and 5 allow us to continue to develop our data entities in DIXF and perform refreshes without losing out progress.

AX 2012 Workflow Originator

 Recently, I had a request to bulk close out 500+ cases that were currently in workflow.  The issue I came to was I couldn't find where the workflow was pulling the 'Workflow originator' from. After some research, I found that it is stored in the SysWorkflowTable and the Originator field.

AX 2012 to D365 Data Upgrade: Incremental sync did not complete successfully

 Well, we are finally doing an upgrade to D365!  It's exciting to get into this chapter of our ERP.

While attempting a data upgrade to D365, I ran into the following error when running the data upgrade 'execute' portion of the script:

AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Incremental sync did not complete successfully. Error: ArgumentException:tableId.

   at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunPartialTableSync>b__23_1(Tuple`2 result)

   at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler)

   at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunPartialTableSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider)

   at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.PartialTableViewSync()

   at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.PartialSync()

   at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()

   at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)

Failed operation step '/DataUpgrade/PreReqs/PartialDbSync'

Process 'K:\AosService\PackagesLocalDirectory\bin\SyncEngine.exe' failed with exit code -1; see error logs for details.

   at Microsoft.Dynamics.Servicing.DataUpgrade.Operations.Operation.RunProcess(String fileName, ArgumentsBuilder args)

   at Microsoft.Dynamics.Servicing.DataUpgrade.Operations.OperationStep.Execute()

I'm not sure, at this point, what the issue is.  Still researching...

Send selected records from Temp Form Datasource to Class

 Recently, I had the need to send selected records of Form datasource that is a Temp table. 

The initial searches led me to the MultiSelectionHelper class with code something like this in my class:

MultiSelectionHelper helper = MultiSelectionHelper::construct();

helper.parmDataSource(_args.record().dataSource());

myTempTable = helper.getFirst();

while (myTempTable.RecId != 0)

{

    ... [LOGIC] ...

    myTempTable = helper.getNext();

}

This did NOT work at all.  I tried every type of table; Temp, InMemory, Regular.  I could not get MultiSelectionHelper to work.

So, I ended up using FormDataSource:

FormDataSource fds;

fds = _args.record().dataSource();

myTempTable = fds.getFirst(true);

while (myTempTable.RecId != 0)

{

    ... [LOGIC] ...

    myTempTable = fds.getNext();

}

Very similar code.  Just be sure to put 'true' in your call to 'getFirst' on the FDS object as that will ONLY retrieve marked / selected records.