@sakthi61
he problem you are encountering is related to the SubstituteKey property in the PXSelector attribute. The SubstituteKey property is used when you want to display a user-friendly field to the end user but store a different value in the database.
In your case, SubstituteKey = typeof(ProjectLocation.locationID) causes the selector to display locationID, and when a record is selected, the locationID value is stored in the database.
When there are duplicate values for locationID, Acumatica gets confused and always picks the first occurrence because it's not designed to handle duplicate keys. The locationID field should be unique.
One solution would be to modify your data to ensure that locationID values are unique. However, if you cannot ensure uniqueness and you want to show a user-friendly name (like description), you could consider creating a concatenated field in your DAC, something like locationDescrAndID, which could be a combination of locationID and description, or any other field that ensures uniqueness. Then, you could use that field as the SubstituteKey.
Here's an example:
#region Location
[PXDBString(50)]
[PXUIField(DisplayName = "Project Location")]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXSelector(
typeof(Search<ProjectLocation.locationDescrAndID>),
DescriptionField = typeof(ProjectLocation.description),
SubstituteKey = typeof(ProjectLocation.locationDescrAndID))]
public virtual string Location { get; set; }
public abstract class location : PX.Data.BQL.BqlString.Field<location> { }
#endregion
In ProjectLocation DAC:
public abstract class locationDescrAndID : PX.Data.BQL.BqlString.Field<locationDescrAndID> { }
[PXDBCalced(typeof(Add<projectLocation.locationID, projectLocation.description>), typeof(string))]
[PXUIField(DisplayName = "Location ID and Description", Enabled = false)]
public virtual string LocationDescrAndID { get; set; }
This is a calculated field which would concatenate the locationID and description fields. Note: You might need to adjust the code based on your requirements and ensure the uniqueness of LocationDescrAndID.
