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 Velixo Reports

February 11, 2021 by Tim Rodman

You can use Velixo Reports and 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 Velixo Reports

Following up on my last post about generating a Comma-Separated List of Purchase Orders for a Vendor using Excel (click here), I thought I’d show how to do the same example using Velixo Reports since Velixo Reports uses regular Excel formulas instead of DAX measure formulas. DAX measure formulas are nice, but they can be like driving a Formula 1 race car. There’s such thing as too much power 😊

Velixo Reports is a 3rd party ISV product for Acumatica. You can learn about their recent release in this webinar video:

I’m not going to cover how to setup a Velixo Reports connection. Just know that my Velixo Connection Name is “c”. I like using “c” because my brain thinks it stands for “connection” and, more importantly, it’s only one letter which takes up the minimum amount of space when using it in an Excel formula.

I’m using the same two Generic Inquiries that we used in the last post that you can read with the link mentioned above.

Let’s use the new (fairly new) GI formula in Velixo to get the list of Vendors from Acumatica and let’s put this formula in cell A1 of a new Excel worksheet:

=GI("c","AP-Vendors",,"Vendor")

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

The GI Velixo Reports formula uses the new Dynamic Array Formula functionality in Excel. This is different from the old dynamic array formulas which were clunky to use and required you to remember to use Ctrl+Shift+Enter on your keyboard every time you edited them. Click here to read more about the new Dynamic Array Formulas in Excel.

Even though we put our formula in A1, notice how it automatically copied down to the cells below.

But I can’t edit the cells below. If I click on A2, you can see that the formula window shows the formula from A1, but it’s grayed out:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

This list of Vendors isn’t sorted yet so let’s sort it.

First, we don’t want it to return the Vendor column heading or else it will get sorted with the Vendors that start with “V” when we apply the sort. So let’s remove the Vendor column heading by making the last parameter in the GI function false. I like to use 0 instead of “false” because it takes up less room:

=GI("c","AP-Vendors",,"Vendor",0)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Now for the sorting part. We can wrap our formula in the SORT function which is one of the new Dynamic Array Formulas in Excel:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Ok, now we have a sorted list of Vendors from Acumatica.

Let’s put another formula in cell B1 that retrieves a list of Purchase Order numbers. We can do this with another GI formula:

=GI("c","PO-PurchaseOrder",,"OrderNbr",0)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

So far this just retrieves a list of all Purchase Orders in Acumatica.

Let’s make the list specific to the Vendor by adding a filter.

The filter format in the GI function uses the OData filter format. Here is a blog post that I wrote a long time ago on the official OData Blog about Acumatica OData, including some OData filter examples:
https://www.odata.org/blog/acumatica-liberates-erp-with-odata-new

And here are some additional OData filter examples:
https://www.augforums.com/forums/acumatica-odata-with-microsoft-excel-and-power-bi/applying-filters-in-odata

“Fun fact” (as my daughter likes to say) about that blog post on the OData Blog. I got invited to write that post by a guy named Gabriel Michaud who was working at Acumatica at the time. I wasn’t working at Acumatica yet, but Gabriel and I would exchange emails about reporting ideas. Gabriel came up with the idea (as far as I know) to enable OData in Acumatica which really excited me so he let me write the blog post on OData.org. Gabriel then went on years later, after leaving Acumatica, to create Velixo Reports. Pretty cool huh?

Velixo makes creating OData filters a little easier using a function called GIFILTER which returns a filter string in the format that OData expects. Let’s temporarily drop a GIFILTER function in cell C1 to see the filter format that OData expects. The OData filter string is now sitting in C1:

=GIFILTER("c","PO-PurchaseOrder","Vendor",A1)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

We can embed the C1 formula in our B1 formula or even simply reference cell C1 in our B1 formula, but I’m going to copy the text from cell C1 into my B1 formula because my brain likes it better that way for some reason (just a style thing, pick what works for you):

=GI("c","PO-PurchaseOrder","Vendor eq 'AASERVICES'","OrderNbr",0)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

AASERVICES only has one Purchase Order so the results aren’t to exciting, but at least we know that our formula is doing something.

Before we start copying the formula down, let’s make it dynamic by replacing “AASERVICES” with a cell reference to A1:

=GI("c","PO-PurchaseOrder","Vendor eq '"&A1&"'","OrderNbr",0)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Now we can paste the formula down Column B. Let’s paste it from B1 to B6:

=GI("c","PO-PurchaseOrder","Vendor eq '"&A6&"'","OrderNbr",0)

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Even though we only pasted down to B6, ALPINEAIR has 3 Purchase Orders so they display as rows down to B8.

The #SPILL! value in B2 is because it wants to display multiple rows of Purchase Orders for AAVENDOR, but it can’t because we have a formula in B3. We didn’t get this behavior in Column A because we only put a formula in A1. In Column B we are putting a formula in each individual cell.

If we paste our formula down to B7, you can see that B6 becomes a #SPILL! value since it can’t display three rows of Purchase Orders, but AMERDOOR has 2 Purchase Orders so they get displayed in B7 and B8 since there is no formula yet in B8:

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

This is great, but these #SPILL! values are kind of annoying. Also, I want to display the Purchase Orders as a comma-separated list of values, not as rows that have the potential to return #SPILL!

Luckily for us, there is an Excel function called TEXTJOIN that can help us here.

Let’s go back up to B1 and wrap our formula in TEXTJOIN:

=TEXTJOIN(", ",1,GI("c","PO-PurchaseOrder","Vendor eq '"&A1&"'","OrderNbr",0))

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Things don’t look any different because AASERVICES only has one Purchase Order, but let’s copy our formula down to B7 and notice how those #SPILL! values get replaced with a nice comma-delimited list of Purchase Orders.

=TEXTJOIN(", ",1,GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0))

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Those “Error! Query returned no results.” cells don’t look very nice so let’s go back up to B1 and modify our formula further to return an empty cell when there is an error. Then copy the B1 formula down to B7.

I wish I knew a nicer way to do this, but this is the best that I can come up with right now:

=TEXTJOIN(", ",1,IF(LEFT(GI("c","PO-PurchaseOrder","Vendor eq '"&A1&"'","OrderNbr",0),5)="Error","",GI("c","PO-PurchaseOrder","Vendor eq '"&A1&"'","OrderNbr",0)))

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

AAVENDOR has a lot of Purchase Orders. How about we only display the first 4 Purchase Orders, then display a “More than 4 POs” message if there are more than 4 Purchase Orders.

We can enhance our formula further like this:

=IF(COUNTA(GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0))>4,"More than 4 Pos",TEXTJOIN(", ",1,IF(LEFT(GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0),5)="Error","",GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0))))

Acumatica Comma-Separated List of Purchase Orders for a Vendor using Velixo Reports

Now the results look a lot cleaner don’t they?

The formula looks pretty ugly, but the nice thing about this compared to the same example using Microsoft Excel with Power BI DAX formulas (click here) is that you don’t have to learn DAX formulas. This is just using regular Excel formulas.

And there you have it, a comma-separated list of Purchase Orders by Vendor in Acumatica!

Update February 19, 2021 from Gabriel: For large datasets, Gabriel recommends returning the data to the spreadsheet, then referencing the spreadsheet in the formulas, because it will perform better since it avoids multiple server-side queries. Also, regarding my long formula, it can be shorter in Velixo 6 which now returns error messages in something that Excel understands as an error message. This allows us to write the last formula above like this in Velixo 6:

=IF(COUNTA(GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0))>4,"More than 4 Pos",TEXTJOIN(", ",1,IFERROR(GI("c","PO-PurchaseOrder","Vendor eq '"&A7&"'","OrderNbr",0),"")))

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

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

  • RE: AUGForums.com Live Suggestions

    Hi @TimRodman, @Toonsix raises a couple of good poin...

    By Carl Brooks, 51 mins ago

  • Shipments Picked and Packed Per Day

    I am working on a request to display how many shipments...

    By lrains, 7 hours ago

  • RE: Customizing AP Checks

    Thanks Harsha! This is the first report I've run into ...

    By Noah, 9 hours ago

  • RE: AUGForums.com Live Suggestions

    @timrodman: It works. Great job. Thanks, Tim. I love in...

    By ToonSix, 15 hours ago

  • RE: AUGForums.com Live Suggestions

    @toonsix If you watch the recording on YouTube us...

    By Tim Rodman, 17 hours ago

  • RE: Add Location (state) to the Invoice GI

    @tinamckean That's the trickiest part unfortunately. I'...

    By Tim Rodman, 18 hours ago

  • RE: Welcome and Introductions

    Welcome @john-rubidge. There were a lot of people from ...

    By Tim Rodman, 18 hours ago

  • RE: Dashboard Tile not summarizing correct information

    @roycelithgo This worked perfectly. Thanks! For a...

    By mlaraiv, 19 hours ago

  • RE: Add Location (state) to the Invoice GI

    @timrodman Thank you! That did the trick. Is there so...

    By tinamckean, 22 hours 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.