Method Community

How do I do the following action on screen load, with existing customer data that may contain invalid province entries?

I want to evaluate whether the BillAddrState field entry for that customer is part of a list, or, is one of the values in the province column of my custom SalesTaxCodeProvince table? I want to base some actions on whether this condition is true or not, so I'm not sure that a simple validation rule for the field will work.

Maybe I should clarify.

I want a dropdown box for "Province" on the Edit Customer page that populates the BillAddrState field in the Customer table, which would be then hidden. The idea is to implement controls on the data entry for provinces.

It's relatively simple to do this for new customers/accounts. But the issue I have is that there may be existing customers with invalid entries in the BillAddressState field (which is synced to QB).

So, on screen load for the Edit Customer screen, I'd like to populate the "Province" dropdown with a blank entry if the existing BillAddrState entry is not one of ON, QC, AB, etc. The dropdown itself uses these distinct values, but I don't think I can validate the BillAddrState entry versus the dropdown list, can I? Do I need to do it versus a table/ If so, how do I build a selection list from distinct entries in a table?

Hi Mike,

Sorry for the late reply.  I'd suggest that on the screen load, to loop through the table that is used for your Province dropdown (I'm assuming Province table) and use a conditional statement to check the screen value for BillAddressState against the values in the table.  You can then put in actions to update those fields based on the condition. (ie. make the field blank if there is no match).

I might also suggest perhaps making a temporary screen that can update the customer records based on the Province dropdown.  You could use a grid to filter and select invalid records and loop throught the grid to update the field.  This might be a quicker way to get your records updated.

- Adam