tag:blogger.com,1999:blog-78173343218944346412024-03-06T01:38:22.425+01:00Anton Venter's Dynamics 365 Finance blogAnton Venterhttp://www.blogger.com/profile/06098919433688060399noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-7817334321894434641.post-16149496607662992192023-05-12T08:09:00.126+02:002023-05-13T09:53:35.853+02:00Microsoft Dynamics 365 Finance - Multi selection on forms with the SysOperation framework<h1 style="text-align: left;">Background</h1><p style="text-align: left;">The SysOperation framework works with a query to fetch any data that will be processed.</p><p style="text-align: left;">In some cases, it is required to only process the selected (marked) records on a form but this is not possible without some extra coding.</p><p style="text-align: left;">I have solved this using a temporary (temp) table. The RecIDs of the selected records are added to the temp table and then the temp table is joined with the query of the data contract class.</p><p style="text-align: left;"><br /></p><p style="text-align: left;">License/disclaimer: Free to use. Use at own risk. No rights or guarantees.</p><p style="text-align: left;"><br /></p><p style="text-align: left;">Let's start.</p><h3 style="text-align: left;"><br /></h3><h3 style="text-align: left;">Temporary table</h3><p style="text-align: left;">The code below uses a temp table and you will to create a temp table with a single RefRecId field. The data type of the field must be <b>Int64</b>, you can then extend from <b>RefRecId</b>.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxHOSh7-At_TluInmDjjAWvb2S57Pit5p2ly1v2G_qizBO-6CsXf61sagkbUZqnHSiUjsmYDar-VxOpq-cBdUwivd4vYsJGhKyjtsefdRGkcX1O_a8mAxFTJGzK3ZskCUOQuN0ZCWt2A3FMlPfZ7JsOqg5gIRyTHTK5qLMPsARZCZn945vlhTDcY26/s255/2023-05-12%2008_30_20-antonwin11%20-%20Remote%20Desktop%20Connection.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="224" data-original-width="255" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxHOSh7-At_TluInmDjjAWvb2S57Pit5p2ly1v2G_qizBO-6CsXf61sagkbUZqnHSiUjsmYDar-VxOpq-cBdUwivd4vYsJGhKyjtsefdRGkcX1O_a8mAxFTJGzK3ZskCUOQuN0ZCWt2A3FMlPfZ7JsOqg5gIRyTHTK5qLMPsARZCZn945vlhTDcY26/s1600/2023-05-12%2008_30_20-antonwin11%20-%20Remote%20Desktop%20Connection.png" width="255" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div>Make sure the Table Type property is <b>TempDB </b>and CreatedBy property is enabled because the temp table query will filter on the current user.<div><div class="separator" style="clear: both; text-align: center;"><br /></div><div><b>Note:</b> You could use one of the standard tables that contains a RefRecId field as long as the Table Type of the table is TempDB and the RefRecId field data type is Int64.</div><div><p style="text-align: left;"><br /></p><p style="text-align: left;">With the SysOperation framework, I usually create a minumum of three classes, the data contract, controller and the business logic or service class. There is also a helper class which is used for the multi selection and joining with the query.</p><p style="text-align: left;"><br /></p><h3 style="text-align: left;">Data contract</h3><p style="text-align: left;"><span style="font-family: monospace; font-size: x-small;">[DataContractAttribute, SysOperationAlwaysInitializeAttribute]</span></p><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;">public final class AVSysOpMultiSelectTemplateContract implements SysOperationInitializable<br /></span><span style="font-family: monospace;">{<br /></span><span style="font-family: monospace;"> private str packedQuery, packedSelectedRecIds;</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public void initialize() //always called because of SysOperationAlwaysInitializeAttribute<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public Query getQuery()<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> return new Query(SysOperationHelper::base64Decode(packedQuery));<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public void setQuery(Query _query)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> packedQuery = SysOperationHelper::base64Encode(_query.pack());<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> //the query specified here is the query shown in the SysOp dialog and also the</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"> //query used in the SysOp service class.<br /></span><span style="font-family: monospace;"> [DataMemberAttribute, AifQueryTypeAttribute('_packedQuery', queryStr(AVCustTable))]<br /></span><span style="font-family: monospace;"> public str parmQuery(str _packedQuery = packedQuery)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> packedQuery = _packedQuery;<br /></span><span style="font-family: monospace;"> return packedQuery;<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public Set getSelectedRecIdSet()<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> if (!packedSelectedRecIds)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> throw error("You have to set the selected RecIds first.");<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> return Set::create(SysOperationHelper::base64Decode(packedSelectedRecIds));<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public void setSelectedRecIdSet(Set _selectedRecIdSet)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> if (_selectedRecIdSet == null)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> throw error("Argument %1 cannot be null.");<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> packedSelectedRecIds = SysOperationHelper::base64Encode(_selectedRecIdSet.pack());<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public boolean isMultiSelected()<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> return packedSelectedRecIds ? true : false;<br /></span><span style="font-family: monospace;"> }</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;">}</span></span></div><p style="text-align: left;"><code><br />
</code></p><h3 style="text-align: left;">Controller</h3><p></p><p style="text-align: left;"><span style="font-family: monospace; font-size: x-small;">[SysOperationJournaledParametersAttribute(true)]</span></p><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;">public final class AVSysOpMultiSelectTemplateController extends SysOperationServiceController<br /></span><span style="font-family: monospace;">{<br /></span><span style="font-family: monospace;"> public static void main(Args _args)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> AVSysOpMultiSelectTemplateController operation = AVSysOpMultiSelectTemplateController::construct();</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> operation.parmLoadFromSysLastValue(false); //called from the UI so do not call getLast, we don't want previous selections etc.<br /></span><span style="font-family: monospace;"> //operation.initializeFromArgs(_args); //can call initializeFromArgs() if menu item contains SysOp execution info. if not, it removes SysOp execution info.<br /></span><span style="font-family: monospace;"> operation.parmShowDialog(true);</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> AVSysOpMultiSelectTemplateContract contract = operation.getDataContractObject();<br /></span><span style="font-family: monospace;"> Debug::assert(contract != null);</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (AVMultiSelectionHelper::isMultiSelected(_args))<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> contract.setSelectedRecIdSet(AVMultiSelectionHelper::getSelectedRecIDSet(_args));<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> else<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> operation.AVModifyQueryFromArgs(_args);<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (operation.startOperation() == SysOperationStartResult::Started) //will return result because we are running synchronous.<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> Counter cntr;<br /></span><span style="font-family: monospace;"> [cntr] = operation.operationReturnValue();<br /></span><span style="font-family: monospace;"> Info(strFmt("Processed %1 selected records.", cntr));</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> //refresh caller here<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public void new(IdentifierName _className = '', IdentifierName _methodName = '', SysOperationExecutionMode _executionMode = SysOperationExecutionMode::Asynchronous)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> super(_className, _methodName, _executionMode);<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> this.parmClassName(classStr(AVSysOpMultiSelectTemplateService));<br /></span><span style="font-family: monospace;"> this.parmMethodName(methodStr(AVSysOpMultiSelectTemplateService, runQuery));<br /></span><span style="font-family: monospace;"> this.parmExecutionMode(SysOperationExecutionMode::Synchronous); //ScheduledBatch: batch job remains visible. ReliableAsynchronous: job is deleted.<br /></span><span style="font-family: monospace;"> this.parmDialogCaption("AV multi select"); //set dialog caption.<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public static AVSysOpMultiSelectTemplateController construct()<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> return new AVSysOpMultiSelectTemplateController();<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public void AVModifyQueryFromArgs(Args _args)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> if (!_args)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> throw error(strFmt("Argument %1 cannot be null.", identifierStr(_args)));<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (_args.dataset() != tableNum(CustTable))<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> throw error(strFmt("Caller buffer must be %1.", tableStr(CustTable)));<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> CustTable custTable = _args.record();</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (custTable.RecId != 0)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> AVSysOpMultiSelectTemplateContract contract = this.getDataContractObject();<br /></span><span style="font-family: monospace;"> Debug::assert(contract != null);</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (contract != null)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> Query query = contract.getQuery();<br /></span><span style="font-family: monospace;"> Debug::assert(query != null);</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (query != null)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> QueryBuildDataSource custTable_ds = query.dataSourceTable(tableNum(CustTable));<br /></span><span style="font-family: monospace;"> SysQuery::findOrCreateRange(custTable_ds, fieldNum(CustTable, AccountNum)).value(queryValue(custTable.AccountNum));<br /></span><span style="font-family: monospace;"> contract.setQuery(query);<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public boolean mustGoBatch()<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> boolean ret;<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> ret = super();<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> return ret;<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public boolean showQuerySelectButton(str parameterName)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> boolean ret;<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> ret = super(parameterName);</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> AVSysOpMultiSelectTemplateContract contract = this.getDataContractObject();<br /></span><span style="font-family: monospace;"> Debug::assert(contract != null);</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (contract.isMultiSelected())<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> ret = false;<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> return ret;<br /></span><span style="font-family: monospace;"> }</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> public boolean showQueryValues(str parameterName)<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> boolean ret;<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> ret = super(parameterName);</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> AVSysOpMultiSelectTemplateContract contract = this.getDataContractObject();<br /></span><span style="font-family: monospace;"> Debug::assert(contract != null);</span></span></div><div style="text-align: left;"><span style="font-size: x-small;"><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;"> if (contract.isMultiSelected())<br /></span><span style="font-family: monospace;"> {<br /></span><span style="font-family: monospace;"> ret = false;<br /></span><span style="font-family: monospace;"> }<br /></span><span style="font-family: monospace;"> <br /></span><span style="font-family: monospace;"> return ret;<br /></span><span style="font-family: monospace;"> }</span><span style="font-family: monospace;"><br /></span><span style="font-family: monospace;">}</span></span></div><p style="text-align: left;"><code><br />
</code></p><h3 style="text-align: left;">Business logic</h3><p></p>
<code><div><span style="font-size: x-small;">public final class AVSysOpMultiSelectTemplateService</span></div><div><span style="font-size: x-small;">{</span></div><div><span style="font-size: x-small;"> //the service method is a class method with a contract as argument. the contract contains the parameters and query (if specified) for the operation.</span></div><div><span style="font-size: x-small;"> //the contract specified here as argument is the contract for our SysOp controller and service classes.</span></div><div><span style="font-size: x-small;"> //this method does not have to return anything but it does return a container to illustrate how to return 'something' from the service method.</span></div><div><span style="font-size: x-small;"> public container runQuery(AVSysOpMultiSelectTemplateContract data)</span></div><div><span style="font-size: x-small;"> {</span></div><div><span style="font-size: x-small;"> Counter cntr;</span></div><div><span style="font-size: x-small;"> AVTmpRecId tmpRecId; //table containing RecIds of the selected records.</span></div><div><span style="font-size: x-small;"> Set tmpRecIdSet; //set containing the RecIds of the selected records.</span></div><div><span style="font-size: x-small;"> Query query = data.getQuery();</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"> if (data.isMultiSelected())</span></div><div><span style="font-size: x-small;"> {</span></div><div><span style="font-size: x-small;"> tmpRecIdSet = data.getSelectedRecIdSet(); //get selected RecIds as a Set.</span></div><div><span style="font-size: x-small;"> AVMultiSelectionHelper::populateTmpRecIdFromSet(tmpRecIdSet, tmpRecId); //populate our temp table from the set that contains the RecIds.</span></div><div><span style="font-size: x-small;"> AVMultiSelectionHelper::modifyQueryAddJoinToTmpTable(query); //modify the query to join with our temp table.</span></div><div><span style="font-size: x-small;"> }</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"> QueryRun queryRun = new QueryRun(query);</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"> if (data.isMultiSelected())</span></div><div><span style="font-size: x-small;"> {</span></div><div><span style="font-size: x-small;"> queryRun.setRecord(tmpRecId); //if joined with a temp table, we must set the record on the queryRun object.</span></div><div><span style="font-size: x-small;"> }</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"> while (queryRun.next())</span></div><div><span style="font-size: x-small;"> {</span></div><div><span style="font-size: x-small;"> CustTable custTable = queryRun.get(tableNum(CustTable));</span></div><div><span style="font-size: x-small;"> info(custTable.AccountNum);</span></div><div><span style="font-size: x-small;"> cntr++;</span></div><div><span style="font-size: x-small;"> }</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"> return [cntr];</span></div><div><span style="font-size: x-small;"> }</span></div><div><span style="font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;">}</span></div><div><br /></div></code>
<br /><h3 style="text-align: left;">
Helper class</h3><div><span style="font-family: monospace; font-size: x-small;">public final class AVMultiSelectionHelper</span></div><div><span style="font-family: monospace; font-size: x-small;">{</span></div><div><span style="font-family: monospace; font-size: x-small;"> public static Set getSelectedRecIDSet(Args _args)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> Set selectedRecIdSet;</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> if (!_args)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> throw error(strFmt("Argument %1 cannot be null.", identifierStr(_args)));</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> if (!_args.dataset())</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> throw error("Must be called with a caller buffer.");</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> if (!_args.record().isFormDataSource())</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> throw error("The caller buffer must be form datasource.");</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> selectedRecIdSet = new Set(Types::Int64);</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> MultiSelectionHelper multiSelectionHelper = MultiSelectionHelper::construct();</span></div><div><span style="font-family: monospace; font-size: x-small;"> multiSelectionHelper.parmDatasource(_args.record().dataSource());</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> Common buffer = multiSelectionHelper.getFirst();</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> while (buffer.RecId != 0)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> selectedRecIdSet.add(buffer.RecId);</span></div><div><span style="font-family: monospace; font-size: x-small;"> buffer = multiSelectionHelper.getNext();</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> return selectedRecIdSet;</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> public static boolean isMultiSelected(Args _args)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> Set selectedRecIdSet = AVMultiSelectionHelper::getSelectedRecIDSet(_args); //arg checks are done in getSelectedRecIDSet().</span></div><div><span style="font-family: monospace; font-size: x-small;"> return selectedRecIdSet.elements() > 1 ? true : false;</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> public static void modifyQueryAddJoinToTmpTable(Query _query)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> QueryBuildDataSource qbds;</span></div><div><span style="font-family: monospace; font-size: x-small;"> SysDictTable dictTable;</span></div><div><span style="font-family: monospace; font-size: x-small;"> QueryBuildDataSource tmpRecId_ds;</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> if (!_query)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> throw error(strFmt("Argument %1 cannot be null.", identifierStr(_query)));</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> qbds = _query.dataSourceNo(1); //join on first datasource in query.</span></div><div><span style="font-family: monospace; font-size: x-small;"> dictTable = SysDictTable::newTableId(qbds.table());</span></div><div><span style="font-family: monospace; font-size: x-small;"> Debug::assert(dictTable != null);</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> tmpRecId_ds = qbds.addDataSource(tableNum(AVTmpRecId), tableStr(AVTmpRecId));</span></div><div><span style="font-family: monospace; font-size: x-small;"> tmpRecId_ds.addLink(dictTable.fieldName2Id(identifierStr(RecId)), fieldNum(AVTmpRecId, RefRecId));</span></div><div><span style="font-family: monospace; font-size: x-small;"> qbds.clearRanges(); //joined so clear all existing ranges.</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> SysQuery::findOrCreateRange(tmpRecId_ds, fieldNum(AVTmpRecId, CreatedBy)).value(curUserId()); </span></div><div><span style="font-family: monospace; font-size: x-small;"> SysQuery::findOrCreateRange(tmpRecId_ds, fieldNum(AVTmpRecId, CreatedTransactionId)).value(queryValue(appl.curTransactionId()));</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> public static void populateTmpRecIdFromSet(Set _recIdSet, AVTmpRecId _tmpRecId)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> if (!_recIdSet)</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> throw error(strFmt("Argument %1 cannot be null.", identifierStr(_recIdSet)));</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> SetEnumerator enum = _recIdSet.getEnumerator();</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-family: monospace; font-size: x-small;"> while (enum.moveNext())</span></div><div><span style="font-family: monospace; font-size: x-small;"> {</span></div><div><span style="font-family: monospace; font-size: x-small;"> _tmpRecId.RefRecId = enum.current();</span></div><div><span style="font-family: monospace; font-size: x-small;"> _tmpRecId.insert();</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"> }</span></div><div><span style="font-family: monospace; font-size: x-small;"><br /></span></div><div><span style="font-size: x-small;"><span style="font-family: monospace;">}</span> </span></div><div><br /></div><div><br /></div></div></div>Anton Venterhttp://www.blogger.com/profile/06098919433688060399noreply@blogger.com0tag:blogger.com,1999:blog-7817334321894434641.post-92088704287752730412021-03-07T11:21:00.032+01:002023-05-12T10:13:27.850+02:00Microsoft Dynamics 365 Finance - OneBox Virtual Machine - How to download VHD files fast with AzCopy.exe<p> </p><h1>Background</h1><p>It's possible to download a complete working Microsoft Dynamics 365 Finance virtual machine (VM) called OneBox from the LifeCycle services (LCS) asset library. By the way, this VM will only run with Hyper-V and will not run with other virtualisation software.</p><p>This VM can run locally on your companys infrastructure or even on your laptop provided you have a solid state disk (SSD) with enough space and your laptop has enough memory. My Dynamics 365 Finance VM takes up about 120GB of disk space on my external SSD drive.</p><p>I enabled Hyper-V on my laptop in Windows features and I am able to run the Dynamics 365 Finance VM with 24GB of RAM allocated to the VM and 8GB RAM for my Windows 10 host. The VM runs okay but it's definitely not suitable for full time development.</p><p>When I downloaded the 12 virtual hardisk (VHD) part files for the first time using with my browser, I discovered that it took a long time to download the files and many of the downloads failed. I had to retry them and it was just painful. Each file is about 3 GB. The separate part files, or volumes, will become one large VHD file when executing the first part file which is an executable.</p><p>In the LCS in asset library, on each downloadable file, I saw a button with the text "<i>Generate SAS link</i>". When I clicked on it, I got a message that the "<i>SAS link for the asset has been copied to my clipboard and that I can use AzCopy to download the the asset</i>".</p><p>I researched AzCopy a bit and downloaded a copy from the Microsoft site to my laptop and extracted it to a folder.</p><p>AzCopy can download files much faster than a browser from BLOB storage would because it uses multi threading to download different parts of the file at the same time. A browser uses a single thread per file being downloaded.</p><p>You can compare it to a race to empty two identical swimming pools, containing exactly the same amount of water manually just using buckets. With the first swimming pool, there is just one person emptying the pool but with the other, there are ten persons. Not really fair is it? But the ten persons will empty the pool a lot faster than one right?</p><p>I selected the first VHD file and clicked on the "Generate SAS link" button. I pasted the link to a text editor to create a command for AzCopy. I opened a command prompt and downloaded the first file using AxCopy.</p><p>To make things easier, I got the idea to add the separate AzCopy command lines in a PowerShell script, one command for each file part . The PowerShell script can download all the part files one after each the other.</p><p><br /></p><p></p><h1>Create PowerShell script</h1><div><ol><li>Download azcopy and extract it in a folder somewhere like c:\azcopy.</li><li>Create a new text file called "downloadvhd.ps1" in the <u>same</u> folder as where azopy.exe is located.</li><li>Log in to LCS and go to the Asset Library of your project.</li><li>Select the "Downloadable VHD" asset type on the left. If you don't have any part files listed on the right, import them from the Shared Library.</li><li>Select the first Part01 VHD file and click on the "General SAS link" button for that file. This will copy the link to the clipboard.</li><li>Open the file created in step 2. with a text editor. On the first line, type: .\<span style="font-family: courier;">azcopy.exe copy '</span></li><li>After the aposthophe, paste the SAS link from the clipboard by pressing Control + V or Paste from the text editor menu. Make sure there is no space between the aposthrophe and the SAS link.</li><li>The first part file is an executable file, the rest of the part files are RAR archive volume files with the .rar extension. After the SAS link, type a closing aposthrophe a space and the destination file name e.g.: <span style="font-family: courier;">' c:\temp\FinandOps10.0.13.part01.exe</span></li><li>For each part file in LCS, create a line in the script file, their file names will be FinandPos10.0.13.part02.rar, FinandPos10.0.13.part03.rar and so on. Important: Click on the "Generate SAS link" button for each file and paste it in the file for the corresponding part file.</li><li>When all part files command are in the script file, save it and execute it.</li></ol></div><div><br /></div><h1>Example PowerShell file</h1><div>Replace SAS-link-for-Partxx with your own SAS links and change the file names.</div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part01' C:\temp\FinandOps10.0.13.part01.exe</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part02' C:\temp\FinandOps10.0.13.part02.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part03' C:\temp\FinandOps10.0.13.part03.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part04' C:\temp\FinandOps10.0.13.part04.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part05' C:\temp\FinandOps10.0.13.part05.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part06' C:\temp\FinandOps10.0.13.part06.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part07' C:\temp\FinandOps10.0.13.part07.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part08' C:\temp\FinandOps10.0.13.part08.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part09' C:\temp\FinandOps10.0.13.part09.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part10' C:\temp\FinandOps10.0.13.part10.rar</span></div><div><span style="font-family: courier; font-size: x-small;">.\azcopy.exe copy 'SAS-link-for-Part11' C:\temp\FinandOps10.0.13.part11.rar</span></div><div><br /></div><h1>Syntax</h1><div><div>azcopy.exe copy [source] [destination]</div><div><br /></div><div>Example:</div><div>azcopy.exe copy '<i>SAS-link-for-Part01</i>' C:\temp\FinandOps10.0.13.part01.exe</div></div><div><br /></div><div><h1>Notes</h1><div>The first part file is an executable file which will extract and combine all the part files into a single VHD file.</div><div><br /></div><div>You have to check the file names in LCS and adjust the file names in the script accordingly to match the version in LCS. Don't use my example version.</div><div><br /></div><div>The SAS link is enclosed in single apostrophies. If the file names contain spaces, then the file name part must also be enclosed with single apostrophies.</div><div><br /></div><h1>Download in progress</h1></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirvtlc2dSNo2WNsI_l4C-GqQR8DP0HHQe7Ubbukc8yhNjQn7Z3_wIl7oQIE_67Po16e14mbvawUU6fBpoJE7Fzv2zi_SkNj7Ck2Av6vC8Ngs9ITnlJLmZ2wdsg_dkkykbTshtSCc9DUNQ/s855/anton-venter-download-vhd-azcopy.jpg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="Download VHD files from LCS with AzCopy" border="0" data-original-height="535" data-original-width="855" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirvtlc2dSNo2WNsI_l4C-GqQR8DP0HHQe7Ubbukc8yhNjQn7Z3_wIl7oQIE_67Po16e14mbvawUU6fBpoJE7Fzv2zi_SkNj7Ck2Av6vC8Ngs9ITnlJLmZ2wdsg_dkkykbTshtSCc9DUNQ/w400-h250/anton-venter-download-vhd-azcopy.jpg" title="Download VHD files from LCS with AzCopy" width="400" /></a></div><br /><div><br /></div>Anton Venterhttp://www.blogger.com/profile/06098919433688060399noreply@blogger.com0Amsterdam, Nederland52.3675734 4.904138924.057339563821152 -30.2521111 80.677807236178836 40.0603889