Use powerquery in excel.
Publich Vendor Ledger Entries in Web Services.
Open Excel and connect from Data -> Get Data -> From Online Services -> Microsoft Dynamics 365
when connected, edit the query to the below code:
PS:
- Replace the bold source variables from your environment.
- You can change the inactive flag to the desired date range
Code:
let
Source = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/[Tenant ID]/[Environment Name]/ODataV4/Company([Company Name])/[Service Name]", null, [Implementation="2.0"]),
#"Selected Columns" = Table.SelectColumns(Source,
{
"Vendor_No",
"Vendor_Name",
"Posting_Date",
"Document_Type",
"Amount_LCY"
}
),
#"Changed Type" = Table.TransformColumnTypes(#"Selected Columns",
{
{"Posting_Date", type date}
}
),
#"Filtered Invoices" = Table.SelectRows(#"Changed Type",
each [Document_Type] = "Invoice"
),
#"Grouped Rows" = Table.Group(#"Filtered Invoices",
{"Vendor_No", "Vendor_Name"},
{
{"Last Invoice Date", each List.Max([Posting_Date]), type nullable date},
{"Invoice Count", each Table.RowCount(_), Int64.Type},
{"Total Amount LCY", each List.Sum([Amount_LCY]), type number}
}
),
#"Added Last Activity" = Table.AddColumn(#"Grouped Rows",
"Last Activity Date",
each [Last Invoice Date]
),
#"Added Inactive Flag" = Table.AddColumn(#"Added Last Activity",
"IsInactive",
each
let
cutoff = Date.AddYears(Date.From(DateTime.LocalNow()), -2)
in
[Last Activity Date] = null or [Last Activity Date] < cutoff,
type logical
)
in
#"Added Inactive Flag"
Regards,
Ralph
