Retriving the data through Dynamics Query in D365 F&O

 Dynamics Query

We use classes to write the Dynamics Queries, dynamic query that can be used to retrieve data from the database. So, in simple terms, a Dynamics Query is like a recipe that tells the system what data you want, where to find it, how to filter it, and how to organize it. When you "cook" the query, you get back the data you need to work with.

X++ code:

The total count shows how many records with the customer name of customer table are in the sales table, totalCounter counts the total number of records that are entered on US-001,

counter counts the number of records that have given US-001.

Table Methods D365 F&O

Table Main Methods

how to create Purchase order - Vendor payment journal in D365 Finance and Operations

PURCHASE ORDER FLOW

Now we are going to create a Purchase order after that Invoice creation later Purchase invoice payment.

Goto -->Account payable--> Purchase order--> All Purchase Orders--> New --> select vendor, Site and warehouse details. Then click Ok.

Next Add item to Purchase Order, give Qunatity, Price and Save it.

Next in ActionPane Selct Purchase--> Actions--> confirm, select confirm.

Next in ActionPane Selct Recieve--> Generate--> Product Receipt, select and give Product name, confirm it.

Next in ActionPane Selct Invoice--> Generate--> Invoice, select Invoice, give Invoice number and update it, match it and post.

Next AP--> Invoice--> Invouce Journal--> Add new Journal

Next AP-->Payments--> Vendor Payments--> Add new Payment Journal--> Go to Lines page-->select Settlement transaction than Ok--> Validate and Post Vendor Payment Journal

SSMS Query:

Used Code:

how to Create Purchase order and how to retreive the main details from Purchase Order

Hi All,

Hope you all doing great, 

Create new Purchase Order,
Added Vendor, Site and Warehouse to it,
Added item to Purchase Order line,
To that item we need to give Quantity, Quantity price,
After that we need to confirm it,
Later we need rise the product receipt and give the product name to it,
After that Generate Invoice for the Purchase Order, give the name to Invoice that we generated,
Later match product receipt, update match status and finally Post the Purchase Invoice.
Purchase Invoice Created.

SQL script:
Scenario: Get the PurchID, InvoiceAccount, Invoiceid , LedgerVoucherID, CurrencyCode, Itemid for the Purchase order which is created.

 

Below is the script I have created with joining the 4 tables.

Simple Queries

 Basic Simple Query

1st query:

I retrieved the Account Number from Custtable and Zipcode from LogisticsPotalAddress,

In INMF entity

Interview Questions for Batch Job monitoring in D365FO X++

Interview Questions

 What is Batch Job Monitoring?

Batch job monitoring in Dynamics 365 Finance and Operations (D365FO) refers to the process of overseeing and managing the execution of batch jobs within the system. In D365FO, batch jobs are often used for tasks that can be run in the background.

What is Batch Job?

A batch job is a predefined task or set of tasks that are executed automatically by the system at a scheduled time or interval.

They can be configured, scheduled, and monitored to ensure they run smoothly and effectively, helping organizations to better manage their business operations.

What is Batch task?

A batch job task, refers to a specific unit of work that is scheduled to be executed automatically at a predefined time or on a recurring basis within the system. These tasks are typically part of larger processes and are grouped together for efficient execution.

In simpler terms, a batch task is like a to-do item for the system.

What is Batch Class?

Batch classes allow administrators to define the parameters and settings for batch tasks, such as scheduling, priority, and dependencies between tasks. This makes it easier to manage and monitor the execution of batch jobs within the system.

What is Recurrence?

Recurrence is a Pattren, Recurrence is defined as when a batch job should run. After setting the Recurrence, Batch jobs will run at a particular time or run repeatedly. at that time, we use recurrence.

OR

 The batch job's recurrence is determined by when it should execute. When batch processes need to run repeatedly or at a specific time, we use recurrence.

What are Batch Alerts, How can you configure Them?

To monitor batch jobs, we can set up alerts. Alerts can be sent when the batch job ended, fails, or finishes. To monitor these batch jobs you can set up alerts. These are sent when the batch job succeeds, fails, or finishes. 

OR

To monitor batch jobs, we can set up alerts. Alerts can be sent when the batch job succeeds, fails, or has finished running.

Can you configure alerts for batch job failures?

Yes, we can configure alerts while creating a batch job we should create the alerts.

Configuring Alerts for Batch Job Failures in Dynamics 365 F&O:

This method is suitable for individual batch jobs. Here's how to do it:

1. Navigate to System administration > Inquiries > Batch jobs.
2. Select the desired batch job from the list.
3. Click on the "Alerts" button on the Action Pane.
4. In the "Batch job alerts" dialog box, configure the following:
   • Alert type: Choose "Custom alert" or "Manager alerts" depending on your preference.
   • Alert message: Enter the message you want to receive in the notification.
   • Condition: Select "Status changes to Error" to trigger the alert upon failure.
   • Recipients: Enter the email addresses of individuals who should receive the alert.
5. Click "OK" to save the configuration.

What are Batch Status?

In order to manage D365 batch jobs, it is very important to understand what each Batch job Status mean. See each description below.

• Withhold: Indicates that the batch job is ‘on hold’ and will not be run.
• Waiting: When a batch job is first created, it starts with a status of ‘Waiting‘. The batch job service will pick up and execute the process at the next recurrence scheduled. The job could be scheduled to run right away. Or, it could be scheduled to run at a certain time in the future. 
• Executing: This indicates the job is actively being executed. 
• Error: Indicates the batch job failed to finish due to an error.
• Ended: This status indicates the batch job has been completed. Typically, this is seen when jobs are set up to run a single time. Batch jobs that are setup to run on a recurrence will change back to a status of ‘Waiting‘ after they have finished a particular run. However, if there is an ‘End Date‘ on the recurrence and that has been reached, the status will change to ‘Ended‘. This just indicates that the overall batch job was completed.
• Ready: When we have to run 30 tasks at a time, the server may take 20 to execute, and the remaining will be in the ready state.
• Didn’t Run: When we cancel the batch job in error status, the dependence tasks will go under the didn’t run status.
• Cancelling: Indicates a user has manually selected to ‘Cancel’ a currently executing batch job. .
• Cancelled: after batching, the job will be successfully cancelled. The cancelled statues will be shown on that batch job status.

What is skipping a Batch Job?

In a batch job, we have more than one task are there, at that time, if we don't want to run any particular task, we use the skip batch task. so that skipped tasks can't run in that batch job for the time being.

How to create a copy batch job?    Can we create copy from copy of batch job?

No, we cannot create copy from copy of batch job, but we can create copy from main batch job.

What is Batch Group?

     A batch group concept used to manage and schedule the processing of batch jobs. Batch groups allow you to group related batch jobs together for efficient scheduling, monitoring, and control. Here's a brief.  

    A batch group is a concept used to manage and schedule the processing of batch jobs. Batch groups allow you to group related batch jobs together for efficient scheduling, monitoring, and control. Here’s a brief overview of how batch groups work in D365FO.

   Grouping: Batch groups are used to group related batch jobs that should be processed together. For example, you can create a batch group for all the financial related batch jobs or for a specific module within D365FO.

What is Active Period?

 Specifying time ranges during which jobs within a batch group can start execution. selecting to run batch jobs outside of office hours only. setting the recurrence for anytime within the active period. For example, you administrator might select to run the batch jobs every hour, but only between the hours of 6:00PM and 8:00AM.

What you mean by Parameters ?

What do you mean by parameters of batch task?

Task Description, Recurrence, Batch Group, Monitoring Category

What is Abort in Batch Job?

If a Batch job is taking to much time to finish or end  at that time we trined to cancle that batch job but sometimes it won't happen becuase of some techinical issues like this times we use the Abort option to quite from executing.

What you will do if any batch taking longer time to finish?

When a Batch job is taking to much time to finish at that time we have try to change the Batch Job statues Execute to Cancle. if that didn't work then we should abort that job.

What is task Dependence?

Within a batch job have one or more batch tasks. Many batch jobs only have one task, but if you have more that one task you can set dependencies so that the tasks are executed in sequence. If a batch job relies on the successful completion of another job before it can run, any failure in the preceding job will cause the dependent job to be skipped.

What is constraints?

When we creatd the task dependence, those dependence task can those into Constraints.

When we created the task dependence between tasks, those task can added into the Constraints. 

Expected status: 

Ended: if the task is ended only the dependence task will run.

Error: if the task status Error only the dependence task will run.

Ended/Error: either ended or Error only the dependence tasks will run

What is HAS condition?

It is related to task Dependence, If we want to add Task Dependence then the Has condition change to "YES".

What is Batch job Log?

When an error acuier in Batch Job that will go into Log, We can see the Batch Job Errors in Log.

What is Batch Job History?

After a batch job is processed, you can view the history. The history includes any messages that were encountered while the job was running.

Batch Job History page displays information about past executions, including successful runs, failures, and skipped instances.

What is bundling?

A batch class gathers related tasks or operations into a single unit, allowing them to be executed together as part of a batch job.

None: no sub tasks.  

Multi thread: sub tasks.

What do you mean by AOS and how to check if AOS is restarted or not?

when the Batch Job status in Error then we have to check the Log after the AOS is restated or not, if the Job Error time and AOS restated time are same then we can assume that, because of  the AOS restart the Error is came.

D365 F&O Relations

 RELATIONS

What is Relation?

A "Relation" refers to a mechanism for establishing connections between tables in the database. In a simple way, you can consider a "Relation" as a link between two tables that allows you to access related data easily. It specifies how records in one table correspond to records in another table.

This linkage is essential for performing operations like querying related data, updating related records, and ensuring data integrity across different tables within the application.

Relations:

• Normal relation:

A normal relation is used to specify field to field relationship. A normal relation is used to specify a relationship without any conditions. 

Ex:  Table1.Field = Table2.Field

• Field Fixed Relation:

This type of relation means that the value in one field is directly related to the value in another field, and this relationship is fixed or predetermined. Without normal relation we can't use the Field Fixed Relation. In a simple way we can say like this, many Perent tables one child table.

Table.Field(Enum) == <EnumValue>

• Related Field Fixed Relation:

A related field fixed relation is similar to a field fixed relation but with an additional level of connection. This type of relation defines a connection between a field in one table and a related field in another table through an intermediate table. Without normal relation we can't use the Related Field Fixed Relation. In a simple way we can say like this, many Child tables one Perent table

<EnumValue> == Table.Field(Enum)

ForgienKey Relations:

a FOREIGN key relation refers to a relationship between two tables where one table (child table) contains a column that refers to the primary key column of another table (parent table). This relationship helps enforce data integrity and ensures that the values in the child table's column correspond to valid values in the parent table's primary key column.

In simpler terms, it's like having a link between two tables where one table's column values are dependent on the values of another table's primary key column.

• Field Fixed Relation:
This type of relation means that the value in one field is directly related to the value in another field, and this relationship is fixed or predetermined. Without normal relation we can't use the Field Fixed Relation. In a simple way we can say like this, many Perent tables one child table.


Table.Field(Enum) == <EnumValue>
• Related Field Fixed Relation:
A related field fixed relation is similar to a field fixed relation but with an additional level of connection. This type of relation defines a connection between a field in one table and a related field in another table through an intermediate table. Without normal relation we can't use the Related Field Fixed Relation. In a simple way we can say like this, many Child tables one Perent table
<EnumValue> == Table.Field(Enum)

How to use JOINs and How to retrieve data by using different JOINs.

JOINS

Created two tables  1^st table: StudentHeader and 2^nd table: StudentPaymentDetails,

Added Fields and inserted the values in both tables,

Added the relations as well,

Created Job for Joins(Inner Join, Outer Join, Exists Join, Not-exists Join).

Note: while writing joins use "While select * from TableName".

StudentHeader: 

StudentPaymentDetails:

Inner Join: It will shows the data from parent and child which is matched in both table. It won’t show the not matched records.

 

X++ code Input:  

Output:

Outer join: It retrieves the data from the both matching records and not matched records from the parent table

 

X++ code:

Output:

Exists Join: It retrieves the records from the parent table only if there exist record in the child table it will stops looking after one match in the child table. 

X++ code:        

Output:

Not-exists Join: Not-exists Join shows the data only from parent table which does not have match with the child table.

X++ code: 

Output:

Add a Button to Sales order Action Pane, If we click on that button it should show the Invoice ID for that Sales Order.

 Process

How to create Sales Order and how to retrieve invoice number for that Sales Order in SSMS

Creation of a Salesorder

Open the front office or front end, then pick the company.

On the left side, we have a Hamburg symbol; click on it and select Accounts Receivable.

Then select all sales orders under the orders tab. The sales order page will open.

Select New on the action pane. From the right side, we can see the sales order creation page. Select the customer name and give the site and warehouse details.

Click OK.

Next, attach the item, units, and unit price to the new sales order. save it.

Later, go to the action pane, click on pick and pack, and under Pick and Pack, select Generate. Under Generate, select Generate, and under Generate, select Generate Picking List.

After clicking Ok, it will show you one pop-up, which is "You are about to post the document without printing it." Select OK.

Next, select "picking list registration" under Generate.

Then it will open the new page to "update" it. Select update all under Update. 

Later that, come back to Sales Order and select your newly created sales order. In the action pane, we have the Invoice tab, which is under Invoice=>Select Generate=>Select Invoice.

it will show you Pop ups to conform the sales order

Invoiced the order.


SQL script:
Scenario: Get the Invoiceid , Salesstatus, AccountNum, AccountName for the Salesorder which is created.

 

I wrote the Query to find InvoiceId, Accountnum and SalesStatus for the perticuler SalesId.