I support several companies, most of which use only a few branches. I have one retail chain with over 100 branches and growing. Is there a way to pull in the combined ending balance for an account/subaccount combination for all branches using the ACCOUNTENDINGBALANCE formula or some other formula? I have tried using the company ID but it returns an error.
0 Votes
7 Comments
Sorted by
S
Steve Gagneposted
27 days ago
Gabriel, thank you. Adding the concatenate string within the ACCOUNTENDINGBALANCE formula worked.
Thank you both. I was able to get the BRANCHLIST function to work for me. What is the exact syntax for using the "-" (minus) operator with BRANCHLIST. I tried a few things and none worked so I'm sure I'm doing something wrong.
0 Votes
Gabriel Michaudposted
27 days ago
Admin
Hi Steve,
An additional suggestion to what Harry described above -- if you want to include everything except a few branches, you can also do something like that:
*;-BRANCHTOEXCLUDE;-ANOTHERBRANCHTOEXCLUDE
The "*" means "everything", and by adding "-" (minus) front of branch codes it will exclude them.
This approach also works with branch list, assuming you want to include 3 companies except a few branches you can also do BRANCHLIST(Connection,"COMPANY1;COMPANY2;COMPANY3")&"-BRANCHTOEXCLUDE"
0 Votes
H
Harry Lewisposted
27 days ago
Admin
Sure.
Here are the three companies (CAPITAL, PRODUCTS, and SERVICES) as defined in my Acumatica:
All I need to do is use the Velixo BRANCHLIST function to get a list of the branches associated with the company I want. I can use that as the BRANCH parameter in my balance function.
So...
In this example, the ACCOUNTENDINGBALANCE function is computing the balance for the branches in my PRODUCTS company (that is, PRODRETAIL and PRODWHOLE).
0 Votes
S
Steve Gagneposted
27 days ago
Thank you for your response. I have a couple dozen other branches in other companies that I need to exclude. I only want to include all the branches for a single company. Is there a way to do that?
0 Votes
H
Harry Lewisposted
27 days ago
Admin
Hi Steve.
If (in your AccountEndingBalance function) you leave the BRANCH parameter blank, you will get the results for all branches.
In this example, I've computed the balance for each of my branches (and used an Excel SUM function to show the total). But I've also computed that total by simply leaving the BRANCH parameter empty.
0 Votes
I support several companies, most of which use only a few branches. I have one retail chain with over 100 branches and growing. Is there a way to pull in the combined ending balance for an account/subaccount combination for all branches using the ACCOUNTENDINGBALANCE formula or some other formula? I have tried using the company ID but it returns an error.
0 Votes
7 Comments
Steve Gagne posted 27 days ago
Gabriel, thank you. Adding the concatenate string within the ACCOUNTENDINGBALANCE formula worked.
0 Votes
Gabriel Michaud posted 27 days ago Admin
Here's a video walkthrough: https://www.loom.com/share/ac6c3170cb574be28302daf6475f2b20
The file that I created during this video is also attached.
Attachments (1)
Example excl....xlsx
16 KB
0 Votes
Steve Gagne posted 27 days ago
Thank you both. I was able to get the BRANCHLIST function to work for me. What is the exact syntax for using the "-" (minus) operator with BRANCHLIST. I tried a few things and none worked so I'm sure I'm doing something wrong.
0 Votes
Gabriel Michaud posted 27 days ago Admin
Hi Steve,
An additional suggestion to what Harry described above -- if you want to include everything except a few branches, you can also do something like that:
*;-BRANCHTOEXCLUDE;-ANOTHERBRANCHTOEXCLUDE
The "*" means "everything", and by adding "-" (minus) front of branch codes it will exclude them.
This approach also works with branch list, assuming you want to include 3 companies except a few branches you can also do BRANCHLIST(Connection,"COMPANY1;COMPANY2;COMPANY3")&"-BRANCHTOEXCLUDE"
0 Votes
Harry Lewis posted 27 days ago Admin
Sure.
Here are the three companies (CAPITAL, PRODUCTS, and SERVICES) as defined in my Acumatica:
All I need to do is use the Velixo BRANCHLIST function to get a list of the branches associated with the company I want. I can use that as the BRANCH parameter in my balance function.
So...
In this example, the ACCOUNTENDINGBALANCE function is computing the balance for the branches in my PRODUCTS company (that is, PRODRETAIL and PRODWHOLE).
0 Votes
Steve Gagne posted 27 days ago
Thank you for your response. I have a couple dozen other branches in other companies that I need to exclude. I only want to include all the branches for a single company. Is there a way to do that?
0 Votes
Harry Lewis posted 27 days ago Admin
Hi Steve.
If (in your AccountEndingBalance function) you leave the BRANCH parameter blank, you will get the results for all branches.
In this example, I've computed the balance for each of my branches (and used an Excel SUM function to show the total). But I've also computed that total by simply leaving the BRANCH parameter empty.
0 Votes
Login or Sign up to post a comment