In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Questions Formulas in Generic Inquiry
Is it possible to pull data from one entry into another one based on a specific criteria?
I have a large list of customers who all share the same branch. That branch is shared by a Business account that has an Owner.
Can I assign that owner to all customers with the same branch using a formula in the generic inquiry so that I don't have to update that field for all customers every time the owner changes?
Reason I am asking is that I have thousand of customers but only about 200 branches and so an update of 200 branches is a lot quicker than an update of 5000+ customers.
Thanks in advance.
Hi I had a similar scenario. I wanted to update the Contact Email address for Customers based on contact type criteria. On the Customer screen AR303000 you have multiple contact details and email address details. The method I used was to create a Generic Enquiry that will extract the data. Step 2 then create an Data Provider, provider type: Generic Inquiry Provider, in the parameters you specify the screen ID for the Generic Enquiry.
Step 3 create the Import scenario to update the Customer record
Hope this work for you! Retha
Thank you for your reply. I am not looking to update the customer details, I am looking at taking the details of one customer and displaying it for all other customers based on a certain criteria. Here is a more detailed explanation:
To start I have around 4000 Customers.
All 4000 Customers have Business Accounts.
Each Business Account is assigned an Owner, that owner is displayed on a "Customer Screen" Generic Inquiry by adding the Business Account table and setting up a join relation.
Additionally, Every customer is assigned a branch and I have setup a default Business Account entry for each branch which is assigned an owner. All customers assigned to the same branch would have the same owner.
Currently, if a branch changes owner, then I need to update all customers individually. This update process takes a long time, the last time I made a change, it took about an hour and a half to complete.
What I am trying to do is to eliminate the need to update the Owner on all Customers except for the 200 Branch related Default Customer/Business Account entries and just pull the owner id data from the default customer/business account entries based on the criteria of having the same branch assignment.
So in other words have the "Owner" column be populated by a formula that says:
If Customer XYZ is assigned Branch ABC, Find Branch ABC's Default Brusiness Account Entry and find the associated Owner and display it for Customer XYZ.
Is that possible?
This seems very possible. A straightforward, albeit manual way would be to add Mass Updating to the GI Business Account GI. Once you change owner for the branch, go to the GI and filter for that branch, then update all of the owners.
Next step would be to add some automation using Business Events and Import Scenarios. You can make a GI to join Customer and Branch together and look for where Customer.Owner <> Branch.Owner. Have some events to check for results in this GI and then have it update by running the import scenario. I know Tim has some good posts going over how they all play together. https://www.augforums.com/forums/everything-else/automatic-task-creation-with-business-event-and-import-scenario/ He demonstrated how to create Tasks, but tweaking the Import Scenario to look at Business Account or Customer screen and updating the Owner shouldn't be too bad.
If you put the condition of Branch=ABC in your GI join, you only get ones that meet your condition. You could then use isnull to show the normal value if the joined table is null.
I'm still a little unclear here if you're looking to actually update data or just display an Owner column that is populated by a formula. Assuming it's the later, I like Ryan's suggestion.