AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Live
  • Login
  • Start Here
  • Rolodex
  • Courses
  • Consulting
  • Register

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

February 2, 2021 by Tim Rodman

You can use Microsoft Excel and OData in Acumatica to get a comma-separated list of Purchase Orders for a list of Vendors.

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Following up on a previous post about a Comma-Separated List of Shipments for a Sales Order using a Generic Inquiry and SQL View (click here), this post will be about generating a comma-separated list using Microsoft Excel.

Most people don’t realize it, but, if you are using the current Office 365 Windows Desktop version of Excel, you have access to the Power BI calculation engine as part of Excel. It’s baked in. You don’t get the fancy visualization layer that you get when using PowerBI.com, but the real “power” in “Power BI” is the calculation engine, not the visualization layer, at least that’s what I think.

So, we can use the Power BI engine inside of Excel without leaving Excel and without installing an Excel Add-In.

For this example, I’m using this Office 365 Windows Desktop version of Excel that came with my $8.25 per month subscription to Microsoft 365 Apps for business (click here).

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

It does need to be the Windows (not MAC) version of Desktop Excel (not Excel Online).

We’ll use two out-of-the-box Generic Inquiries for this example: Purchase Orders (PO3010PL) and Vendors (AP3030PL).

We need to enable both of these Generic Inquiries for OData using the Expose via OData checkbox on the Generic Inquiry (SM208000) screen:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel
Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Then we can go into Excel and connect to both of these Generic Inquiries using OData by going to Data -> Get Data -> From Other Sources -> From OData Feed:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

I’m using my local Acumatica Instance which only has one Tenant so my OData URL looks like this:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Login with your Acumatica Username and Password. Hopefully you’re using the https protocol so you won’t get the “The password won’t be encrypted when sent.” message that I’m getting:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Check Select multiple items and check the two Generic Inquiries mentioned earlier. Then, don’t click Load, but click the down arrow next to Load and choose Load -> Load To…

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Here’s where the Power BI part comes in. Rather than load the data directly into the old-fashioned Excel worksheet, we can load it into the Data Model by choosing Only Create Connection and checking Add this data to the Data Model:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

If you look on the bottom, you’ll see that Excel is retrieving the data from Acumatica:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Once Excel is finished retrieving the data, you’ll see what appears to be an empty spreadsheet. Where did the data go? On the right you can see that 130 rows were loaded from AP-Vendors and 1,311 rows were loaded from PO-PurchaseOrder, but where did the data go?

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

If I look at the file size, it’s 821KB which is not insignificant. The empty Excel file when I started was only 9KB.

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

The file size got a lot bigger because the data is stored in the .xlsx Excel file. You just can’t see it in the worksheet.

To see the data, you can use this little green icon on the Data ribbon. The popup says “Go to the Power Pivot Window”. Power Pivot (formerly called PowerPivot) is the old name for Power BI.

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

A separate window pops up which makes it feel like you are leaving Excel, but you aren’t. Everything you see here is stored in the .xlsx file. Ah, here’s my data!

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

We need to create a relationship between our two data tables. To do this, go to Home -> Diagram View and drag your mouse between the Vendor field in both tables. It will draw a line between the Tables for you automatically.

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Now we can close the separate window and return to our regular Excel window.

We need to create a Pivot Table so go to Insert -> PivotTable:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Rather than create a regular Pivot Table based on a range of cells (the old way), we’re going to create a Pivot Table using the Power BI functionality (the new way) by selecting Use this workbook’s Data Model:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Drag the Vendor field from the AP-Vendors table into the Rows area and drag the OrderNbr field from the PO-PurchaseOrder table into the Values area like this:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Now we have a Pivot Table which is counting the number of Purchase Orders for each Vendor.

This is nice, but I want a comma-separated list of values (Purchase Orders) for each Vendor.

To accomplish this, we need a Measure. Let’s create a Measure by right-clicking on either table and choosing Add Measure…

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

I’m naming my Measure POs and using the following DAX formula:

=CONCATENATEX('PO-PurchaseOrder',[OrderNbr],", ")

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Drag the POs measure into the Values area and you should get a nice Pivot Table with a comma-delimited list of Purchase Orders for each Vendor:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

But some of these Vendors have a lot of POs! Maybe I only want to see the list of Purchase Orders if there are 4 or less.

If there are more than 4 Purchase Orders, how about let’s display “More than 4 POs” so we don’t wind up with a really long list which forces us to scroll to the right to see them all.

Let’s right-click on the Measure and Edit Measure… so we can modify the DAX formula:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Let’s make our DAX formula a little more complicated, but not a lot more complicated.

We just want to add a check to see if the number of POs is more than 4 using this formula:

=IF(DISTINCTCOUNT('PO-PurchaseOrder'[OrderNbr])>4,"More than 4 POs",CONCATENATEX('PO-PurchaseOrder',[OrderNbr],", "))

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

Ah, now doesn’t that look a lot better?

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel

There you have it, a comma-separated list of Purchase Orders for each Vendor in Acumatica using Microsoft Excel!

Filed Under: Acumatica Learning Tagged With: Acumatica, Acumatica Blog, Acumatica Generic Inquiries, Acumatica Learning, Acumatica Reporting Tools, Acumatica Training, Microsoft Excel, Power BI

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 41: Nathan Neuberg, from CTO at Cali Framing to Consultant at PC Bennett, sharing his Acumatica Journey (Podcast) February 27, 2021
  • Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports February 11, 2021
  • EP 40: Gustavo Carrasquillo, Solutions Developer at Blanco Group, sharing his Acumatica Customer Story (Podcast) February 9, 2021
  • Acumatica Comma-Separated List of Purchase Orders for a Vendor using Microsoft Excel February 2, 2021
  • EP 39: Update on Acumatica User Groups from Joel Gress (Podcast) February 1, 2021
Acumatica Learning Resources

Recent Forum Posts

  • Support for OData and Apple version of MIcrosoft Excel - Looks like its coming

    See Microsoft link/p> Apparently its in beta.

    By Jeffrey Patch, 1 hour ago

  • RE: Generic Inquiry - CUSTOMER´S BALANCE

    HI @john-rubidgeThanks for your help. The reference he...

    By OskJeron, 8 hours ago

  • RE: AUGForums.com Live Suggestions

    Topics proposed for future meetings: The hybrid of ...

    By ToonSix, 9 hours ago

  • RE: AUGForums.com Live Suggestions

    Hi Ryan and Tim: I have enjoyed the live meeting of 3...

    By ToonSix, 9 hours ago

  • AUGForums.com Live Suggestions

    AUGForums.com is a live broadcast on YouTube Live. ...

    By Tim Rodman, 13 hours ago

  • RE: Customer Location on Statement

    I got it after adding the AR Invoice table to the repor...

    By Bodonna, 13 hours ago

  • Customer Location on Statement

    Has anyone add the customer location to the documents o...

    By Bodonna, 14 hours ago

  • RE: Mass Update Salesperson for Group of Customers

    Update to Issue: After doing some more searches on de...

    By mcondon64, 1 day ago

  • RE: Welcome and Introductions

    @steveskok Workday definitely looks interesting, althou...

    By Tim Rodman, 1 day ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.