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
about 17 hours 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
about 23 hours 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
1 day 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
1 day 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
1 day 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 about 17 hours ago
Gabriel, thank you. Adding the concatenate string within the ACCOUNTENDINGBALANCE formula worked.
0 Votes
Gabriel Michaud posted about 17 hours 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 about 18 hours 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 about 23 hours 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 1 day 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 1 day 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 1 day 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