Solved

Business Event with custom DAC pointing to a SQL View

  • 31 January 2022
  • 4 replies
  • 223 views
N
Rank
Userlevel 2
Badge +1

Hello,

 

I have a situation where a user needs to be notified whenever the credit limit utilisation of a customer exceeds a certain threshold. I have tried using an existing GI which Acumatica offers out-of-the-box with title ‘AR-CreditLimitCustomers’ and added a field to calculate the utilisation percentage. The calculation is as below.

=((([ARBalancesSharedCredit.CurrentBal]+[ARBalancesSharedCredit.UnreleasedBal]+[ARBalancesSharedCredit.TotalOpenOrders]+[ARBalancesSharedCredit.TotalShipped]-[ARBalancesSharedCredit.TotalPrepayments])) / [CustomerSharedCredit.CreditLimit]) * 100

 

However, as expected, the business event does not trigger due to a known limitation that business events do not work on calculated fields.

 

For this reason, I created a SQL View which basically runs the same query as in the GI above and included the custom formula as well as a separate field. I created a DAC linked to this SQL view and added it to a new GI and a business event to it linked to this field.

Despite having this calculated field in a SQL View, the Business event is still not working.

 

Any idea what the issue might be please? Could this be another known limitation?

 

Thanks.

 

 

icon

Best answer by Yuri Karpenko 31 January 2022, 23:31

View original

4 replies

Yuri Karpenko
Rank
Userlevel 7
Badge +6

@npace29 , what is the trigger for the business event? is it a record change / insert or a schedule? I would recommend you try putting this BE on a schedule and see if anything is picked up. Also, if you would like to, you can share your GI, BE, and Import Scenario (and maybe Schedule) for me to review and provide feedback. Thanks!

Yuri Karpenko | DM me if you need help building e-Commerce for your business or connecting it to Acumatica ERP
N
Rank
Userlevel 2
Badge +1

Hi @yurik05,

 

The trigger is on record change when the new value for the Credit Utilisation % field (an underlying calculated field in the SQL View which is available in the DAC) is greater or equal to 30 in my example.

Kindly see the below screenshots. I can’t provide the Generic Inquiry XML since its tied to a custom DAC that I have so it wont work unless the SQL View exists on your machine, however I have included a screenshot below to show the output.

 

 

 

 

Thanks,

Nigel

Yuri Karpenko
Rank
Userlevel 7
Badge +6

@npace29  Nigel, thanks for sharing the details. The issue is that you’re using SQL view. SQL view does not constitute a ‘record’ in Acumatica. Therefore, changes to the underlying entities (records) does not trigger the event. I would try a couple options to overcome this limitation:

  1. You can put this BE on a schedule. In this case, it doesn’t matter what the GI is based on. And then use conditions in the Schedule to filter out lines that do not match your conditions (even use the calculated field).
  2. Add the original data source to the GI (Customer, ARBalances). And trigger the BE every time the Credit Limit (Customer) or Current Balance (ARBalances) changes.

I hope this will help you moving in the right direction.

Best,

Yuri

Yuri Karpenko | DM me if you need help building e-Commerce for your business or connecting it to Acumatica ERP
N
Rank
Userlevel 2
Badge +1

@yurik05 Thanks for your suggestions. Much appreciated :) 

I have tried option 1 yesterday and it worked. It is a good workaround, albeit I do hope that business event triggers on calculated fields become available in the future as it would help a lot in giving more flexibility to the users.

 

Thanks again and regards,

Nigel

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved