Retrieving Subaccount Segment Names

Created by Damien Zwillinger, Modified on Tue, 16 Jul at 9:34 AM by Gabriel Michaud

Applies to:

  • Velixo NX
  • Velixo Classic
  • Acumatica, MYOB Acumatica, Cegid XRP Flex


Overview


It is very common to design reports which show both accounts and subaccounts. It can definitely help to include the names of the accounts, so as to increase readability of the report.

segment_names00.png

Likewise, it would be convenient to also display the names of one or more subaccount segments, as they are defined in the ERP:

segment_names02.png

With a little extra effort, up front, this can easily be accomplished.

 

Step-by-Step


1. Import the custom Generic Inquiry

Import the attached Generic Inquiry to your ERP instance (it returns the contents of ERPs SegmentValue table):

 

segment_names03.png

 

2. Configure the Generic Inquiry Keys

The first time you add a reference to the new Generic Inquiry to your spreadsheet, you will receive an error message letting you know that the Generic Inquiry keys have not been configured:

segment_names04.png

 

On the Velixo0 ribbon of the Excel toolbar, click the Options button:

 

Next, double-click on the name of the Generic Inquiry:

segment_names06.png

 

and then click on the button to edit the key fields for the inquiry:

segment_names07.png

 

Use the >> button to move (in order) SegmentedKeyID, SegmentID, and Value from the Available Fields to the Key Fields:

segment_names08.png

and then press the OK button.

 

 

Press OK on the Velixo-SegementValues Options dialog:

segment_names09.png

 

and press OK on the Options dialog.

Your report will refresh and your GILOOKUP function will now return a value.

 

 

3. Enter the GILOOKUP function

Let's assume that we want the description of the first segment "ELE" from the above example.

That function would look like this:

=GILOOKUP("Demo","Velixo-SegmentValues","Description","SUBACCOUNT",1,"ELE")

  • Demo - would be replaced by the name of your connection

  • 1 - indicates that we are interested in the first segment

  • ELE - indicates the specific subaccount first segment for which we want the description

Since in our example the subaccount is listed in a cell, we can use Excel's text manipulation capabilities to extract it:

=GILOOKUP("Demo","Velixo-SegmentValues","Description","SUBACCOUNT",1,LEFT(D27,3))

 

 

4. Complete the Report

We can now add the GILOOKUP functions to the rest of our report:

segment_names10.png

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article