Question

Field dependencies


Hi All,
 
We were wondering if we are allowed to use field dependencies in Acumatica. An example of field dependency would be that if a value of field A changes, then change the value of field B. Here’s an example for better understanding: If the Tax Zone of a Business Account changes which is extended to Customer and has locations in it, then we will want to change the tax zone field for all associated customer locations as well. 
Please let us know if you need any further clarifications about this. Thanks in advance!
 
Best Regards,
Amruta

19 replies

Hi Amruta! Field dependencies are widely used in Acumatica, and are easy to implement. My favorite way to do so is typically through the PXFormula attribute, I’ve included a code snippet below that shows how this might work for fields on the same table, just by using attributes.

namespace MyCustomization.DAC
{
[Serializable]
[PXCacheName("My Custom Table")]
public class MyCustomDAC : IBqlTable
{
#region Keys and other fields
// ...
#endregion
#region PrimaryField

public abstract class primaryField : BqlString.Field<primaryField> { }

[PXDBString]
[PXUIField(DisplayName = "Primary Field!")]
public virtual string PrimaryField { get; set; }

#endregion
#region DependentField

public abstract class dependentField : BqlString.Field<dependentField> { }

[PXDBString]
[PXDefault("The default value")]
[PXUIField(DisplayName = "Dependent Field!")]
[PXFormula(typeof(Default<primaryField>))]
public virtual string DependentField { get; set; }

#endregion
}
}

The PXFormula attribute on the dependent field ties it to the primary field, and fires the defaulting event for the dependent field every time the primary field changes (don’t forget to set CommitChanges to true on the primary field when adding to the screen if you need immediate screen updates).

You can perform most of the logic you need to in the PXDefault attribute, but in some cases you will need to create a FieldDefaulting or FieldUpdating event on the graph for more complex logic, and in your case I think that the FieldUpdating event would fit best. I’ve included some generic code below that you can change to achieve the result you are looking for.

namespace MyCustomization.Graph
{
public class MyCustomGraph : PXGraph<MyCustomGraph>
{
public SelectFrom<PrimaryDAC>.View PrimaryDocument;

public SelectFrom<DependentDAC>.View DependentRecords;

#region Actions
#endregion
#region Events

protected virtual void _(Events.FieldUpdated<PrimaryDAC, PrimaryDAC.primaryField> eventHandler)
{
PrimaryDAC row = eventHandler.Row;
if (row is null) return;

UpdateDependentFields(row);
}

#endregion

private protected virtual void UpdateDependentFields(PrimaryDAC row)
{
var dependentRecordCollection = DependentRecords.Select();

foreach (var dependentRecord in dependentRecordCollection)
{
dependentRecord.DependentField = "My new value";
DependentRecords.Update(dependentRecord);
}
}
}
}

Let me know if you have any questions implementing these solutions, or if I wasn’t clear on how to achieve the result that you wanted!
 

Code Gist

Userlevel 1
Badge

Sean, Thanks for writhing this out it has been a major roadblock for me to get some of this logic created in the Service Module.  I am having some trouble following some of what you wrote above though and was hoping you could clarify a bit. 

 

In the lower example are you creating a new table that your referencing for matching the values?  For my use case I am trying to create simple logic that defaults a project based on the Customer on the Service order. 

 

I was hoping to just be able to put a formula in under customize attributes of the field but I have no idea of the syntax to start.  Any help would be appreciated…  Thanks,  Greg

 

Hi Greg!

I wanted to keep the code as generic as possible above, but I can definitely give you some concrete examples! I’m not very familiar with the Service Module, but looking at the code for the FSServiceOrder DAC and the ServiceOrderEntry graph, it looks like the ProjectID field is updated when the service contract is changed. It can also default in a value based on the ‘ProjectDefault’ attribute on the DAC, although in this case there is nothing passed in to the attribute constructor, so it should default the ‘No-Project’ id before being overridden by the logic in the BillServiceContractID FieldUpdated event if certain configurations are set up.

 

This may change based on your version or configuration, but I believe using the CustomerID FieldUpdated event should work for you. Snippet and Gist link below.

 

using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;
using PX.Objects.FS;

namespace MyCompany.DefaultProjects
{
public class ServiceOrderEntryExtDefaultProject : PXGraphExtension<ServiceOrderEntry>
{
public static bool IsActive() => true;

#region Actions
#endregion
#region Events

protected virtual void _(Events.FieldUpdated<FSServiceOrder, FSServiceOrder.customerID> eventHandler)
{
FSServiceOrder row = eventHandler.Row;
if (row is null || row.CustomerID is null) return;

Location serviceOrderLocation = SelectFrom<Location>
.Where<Location.bAccountID.IsEqual<@P.AsInt>
.And<Location.locationID.IsEqual<@P.AsInt>>>
.View.Select(Base, row.CustomerID, row.LocationID);

if (serviceOrderLocation is not null && serviceOrderLocation.CDefProjectID is not null)
eventHandler.Cache.SetValueExt<FSServiceOrder.projectID>(row, serviceOrderLocation.CDefProjectID);
}

#endregion
}
}

 

Code Example - Gist

 

This will default the project when the CustomerID is changed, to whatever the default project is for the customer location. You can either compile a dotnet project for this customization, or you can go to your customization project, and select the ‘Code’ option from the left hand menu, and create a new code file that you can paste the snippet into.

Userlevel 1
Badge

Not sure what I’m doing wrong but this is what I get when I compile your code.

 

Building directory '\WebSiteValidationDomain\App_RuntimeCode\'.
\App_RuntimeCode\ServiceOrderEntry.cs(22): error CS1026: ) expected
\App_RuntimeCode\ServiceOrderEntry.cs(22): error CS1002: ; expected
\App_RuntimeCode\ServiceOrderEntry.cs(22): error CS1002: ; expected
\App_RuntimeCode\ServiceOrderEntry.cs(22): error CS1513: } expected
\App_RuntimeCode\ServiceOrderEntry.cs(22): error CS1026: ) expected
Compiler time, in seconds: 1.979358
Validation failed.

Turns out even when using .NET 4.8 Acumatica doesn’t allow you to use C# 9.0 through the customization explorer, I apologize as I didn’t think to test that first. I have replaced the comparisons ‘is’ and ‘is not’ with == and != to avoid the issue you are seeing.

 

using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;
using PX.Objects.FS;

namespace MyCompany.DefaultProjects
{
public class ServiceOrderEntryExtDefaultProject : PXGraphExtension<ServiceOrderEntry>
{
public static bool IsActive() => true;

#region Actions
#endregion
#region Events

protected virtual void _(Events.FieldUpdated<FSServiceOrder, FSServiceOrder.customerID> eventHandler)
{
FSServiceOrder row = eventHandler.Row;
if (row == null || row.CustomerID == null) return;

Location serviceOrderLocation = SelectFrom<Location>
.Where<Location.bAccountID.IsEqual<@P.AsInt>
.And<Location.locationID.IsEqual<@P.AsInt>>>
.View.Select(Base, row.CustomerID, row.LocationID);

if (serviceOrderLocation != null && serviceOrderLocation.CDefProjectID != null)
eventHandler.Cache.SetValueExt<FSServiceOrder.projectID>(row, serviceOrderLocation.CDefProjectID);
}

#endregion
}
}

 

Userlevel 1
Badge

Sean thanks for helping with that!  It will make a big difference for my users!  and I hope my example didn’t hijack the thread and others learned as much as I did. 

Not a problem Greg, I’m happy to help! Let me know if you have any other questions.

Hi Sean, Thanks a lot for pointing us in the right direction. My organizations is fairly new to Acumatica and we are making our way into it and we would appreciate all the help we can get. In our example, we have a custom field defined in Business Account > General tab called as “Tax or VAT Exempt” and we are trying to sync it with Business Account > Shipping tab > Tax Zone field. Both the selector fields have the same list of values to choose from. When the value changes in “Tax or VAT Exempt” field, we need it to update the “Tax Zone” field in Shipping tab.

We were able to get the “New DAC” file in Code of customization project set to the following content. We do not see any errors during compilation of this:

using System;
using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;
using PX.Objects.FS;

namespace ITHAKASugarFields
{
[Serializable]
[PXCacheName("TaxFieldDependencies")]
public class TaxFieldDependencies : IBqlTable
{
#region Keys and other fields
// ...
#endregion
#region PrimaryField

public abstract class primaryField : BqlString.Field<primaryField> { }

[PXDBString]
[PXUIField(DisplayName = "Tax or VAT Exempt!")]
public virtual string PrimaryField { get; set; }

#endregion
#region DependentField

public abstract class dependentField : BqlString.Field<dependentField> { }

[PXDBString]
[PXDefault("EXEMPT")]
[PXUIField(DisplayName = "Tax Zone!")]
[PXFormula(typeof(Default<primaryField>))]
public virtual string DependentField { get; set; }

#endregion
}
}

We have also set “Commit Changes” set to “True” in the screen for the “Tax or VAT Exempt” field. We were also able to start working on the “New Graph” file in Code of customization project and it is set with the following content. We are getting compilation errors on this one.

 

using System;
using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;
using PX.Objects.Location;
using PX.Objects.FS;

namespace ITHAKASugarFields
{
public class TaxFieldDependGraph : PXGraph<TaxFieldDependGraph>
{
public SelectFrom<BAccount>.View PrimaryDocument;

public SelectFrom<BAccount>.View DependentRecords;

#region Actions
#endregion
#region Events

protected virtual void _(Events.FieldUpdated<BAccount, BAccount.edCTaxZoneID> eventHandler)
{
PrimaryDAC row = eventHandler.Row;
if (row is null) return;

UpdateDependentFields(row);
}

#endregion

private protected virtual void UpdateDependentFields(BAccount row)
{
var dependentRecordCollection = DependentRecords.Select();

foreach (var dependentRecord in dependentRecordCollection)
{
dependentRecord.DependentField = "EXEMPT";
DependentRecords.Update(dependentRecord);
}
}
}
}

We see the following errors during compilation:

Validating the website c:\deployment\sites\SB00002090\Customization\SB00002090\siterootValidation\siterootWebsite
IIS APPPOOL\SB00002090
Building directory '\WebSiteValidationDomain\App_RuntimeCode\'.
\App_RuntimeCode\TaxFieldDependGraph.cs(6): error CS0234: The type or namespace name 'Location' does not exist in the namespace 'PX.Objects' (are you missing an assembly reference?)
\App_RuntimeCode\TaxFieldDependGraph.cs(31): error CS8107: Feature 'private protected' is not available in C# 7.0. Please use language version 7.2 or greater.
\App_RuntimeCode\TaxFieldDependGraph.cs(21): error CS0426: The type name 'usrTaxorVATExempt' does not exist in the type 'BAccount'
\App_RuntimeCode\TaxFieldDependGraph.cs(6): error CS0234: The type or namespace name 'Location' does not exist in the namespace 'PX.Objects' (are you missing an assembly reference?)
Compiler time, in seconds: 3.3898453
 
Validation failed.

 

Here are our following questions:

  1. What are the allowed values for type or namespaces in Acumatica? It looks like it can not find “Locations”
  2. It is unable to find our custom field in BAccount namespace. We are looking for “usrTaxorVATExempt” field.

Thanks again for all your detailed responses! Let us know if you need any clarifications.

Best Regards,
Amruta

I’m sorry, that code was meant to be generic and not copied as I didn’t have enough information to fill out any code that would work as you needed, and the code I provided won’t actually accomplish anything copied as is. It was meant as more of a guideline. I’ll take a look later today during lunch and give you a working code example.

Userlevel 6
Badge +9

 

@Amruta Please find the details below

  1. What are the allowed values for type or namespaces in Acumatica? It looks like it can not find “Locations”  →  using PX.Objects.CR;
  2. It is unable to find our custom field in BAccount namespace. We are looking for “usrTaxorVATExempt” field.  →  using PX.Objects.CR;

usrTaxorVATExempt → will show this with underline error (NOT a problem), since you have written this in App_runtime folder. This field will recognized at runtime only.

Thanks Sean. Looking forward to your example!

Thanks Naveen for taking a look at it. We have already included “using PX.Objects.CR;” in our imports. And the validation of the project fails. Should we write this in any other folder for it to not throw errors while compilation?

Best Regards,
Amruta

Userlevel 6
Badge +9

Hi @Amruta  In App_Runtime folder also code will compile and throws an errors if we have any syntax errors except the extended DAC fields (these fields will NOT be recognized at compile time)

If possible, can you share the code → TaxFieldDependGraph so that I can check and let you know.

Hi @Naveen B , 

The “New Graph” file in Code of customization project is as follows. We are getting compilation errors on this one.
 

using System;
using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;
using PX.Objects.Location;
using PX.Objects.FS;

namespace ITHAKASugarFields
{
public class TaxFieldDependGraph : PXGraph<TaxFieldDependGraph>
{
public SelectFrom<BAccount>.View PrimaryDocument;

public SelectFrom<BAccount>.View DependentRecords;

#region Actions
#endregion
#region Events

protected virtual void _(Events.FieldUpdated<BAccount, BAccount.usrTaxorVATExempt> eventHandler)
{
PrimaryDAC row = eventHandler.Row;
if (row is null) return;

UpdateDependentFields(row);
}

#endregion

private protected virtual void UpdateDependentFields(BAccount row)
{
var dependentRecordCollection = DependentRecords.Select();

foreach (var dependentRecord in dependentRecordCollection)
{
dependentRecord.DependentField = "EXEMPT";
DependentRecords.Update(dependentRecord);
}
}
}
}

We see the following errors during compilation:

Validating the website c:\deployment\sites\SB00002090\Customization\SB00002090\siterootValidation\siterootWebsite
IIS APPPOOL\SB00002090
Building directory '\WebSiteValidationDomain\App_RuntimeCode\'.
\App_RuntimeCode\TaxFieldDependGraph.cs(6): error CS0234: The type or namespace name 'Location' does not exist in the namespace 'PX.Objects' (are you missing an assembly reference?)
\App_RuntimeCode\TaxFieldDependGraph.cs(31): error CS8107: Feature 'private protected' is not available in C# 7.0. Please use language version 7.2 or greater.
\App_RuntimeCode\TaxFieldDependGraph.cs(21): error CS0426: The type name 'usrTaxorVATExempt' does not exist in the type 'BAccount'
\App_RuntimeCode\TaxFieldDependGraph.cs(6): error CS0234: The type or namespace name 'Location' does not exist in the namespace 'PX.Objects' (are you missing an assembly reference?)
Compiler time, in seconds: 3.3898453
 
Validation failed.

Thanks a lot for giving us so much of your time.

Userlevel 6
Badge +9

Please check with this…but still I have a below questions and highlighted in the screenshot

using PX.Data;
using PX.Data.BQL.Fluent;
using PX.Objects.CR;

namespace ITHAKASugarFields
{
public class TaxFieldDependGraph : PXGraph<TaxFieldDependGraph>
{
public SelectFrom<BAccount>.View PrimaryDocument;
public SelectFrom<BAccount>.View DependentRecords;

#region Events

protected virtual void _(Events.FieldUpdated<BAccount, BAccount.usrTaxorVATExempt> eventHandler)
{
BAccount row = eventHandler.Row;
if (row is null) return;
UpdateDependentFields(row);
}

#endregion

public virtual void UpdateDependentFields(BAccount row)
{
foreach (var dependentRecord in DependentRecords.Select())
{
dependentRecord.DependentField = "EXEMPT";
DependentRecords.Update(dependentRecord);
}
}
}
}

 

NO syntax errors expect the extended/DAC fields

 

Naveen is correct here, it looks like your primary records and dependent record views are using the same table, and you need to update your FieldUpdated event to use the DAC extension for the second type parameter. If you have a GitHub account, uploading the source code might give us a clearer idea of what your code looks like, and give us the names of some of the custom code you are deploying like the DAC extension.

Thanks a lot Naveen and Sean. We were not sure if a change made to a field via an API would also trigger update on it’s dependent field. Any thoughts?

Userlevel 6
Badge +9

Yes.. If the dependent field triggering from the screen level when we enter the details in the screen, the same behavior will be there from the API as well.

That’s really good to know. We have been currently using APIs to create and update accounts. It looks like a lot of learning curve with being able to use field dependencies. We have a deadline approaching really soon, so we ended up deciding that we will use the additional API call to update using locations endpoint instead of using field dependencies. Once we meet the deadline, we might try to accomplish this to improve our code and reduce the API calls we are making to Acumatica. 

My team and I are really thankful to you both for helping us this far! We will get back to this thread when we are improving the code and add if we have any questions. 

 

Userlevel 1
Badge

This thread has been a big help to me to thank you all for that!  I do have a followup that I am unsure how to bridge based on the above logic.  I am working with Service Order / Appointment entry screens as stated above and I am trying to populate a PO in the FSServiceOrder.CustPORefNbr field based on the FSServiceOrder.Project  and FSAddress.PostalCode  I don’t have a table with the zip code list in Acumatica yet but assume I need to set one up and that looks straight forward to me.  The fields I will use in it are ProjectID / Zip Code and PO Number. 

The part I need help with is the logic to update based on 2 fields from related tables and then populate from a third custom table.  I’m not sure that the task can be accomplished easily but I was hoping some feedback here could start me in the right direction. 

 

Reply


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 — 2020  Acumatica, Inc. All rights reserved