First a bit of background information:
I have set up a base for our credit & collections department to upload customer credit applications and track the progress of their references, etc. I have two tables: Credit Applications and Responses.
The primary field of Credit Applications is a formula which concatenates the Customer Number and Customer Name (i.e. “1000: CUSTOMER NAME”) If it is a brand new customer that does not have a number, the word “NEW” is used (i.e. “NEW: CUSTOMER NAME”).
The primary field of Responses is a formula that calls the record name from Credit Applications once the records are linked.
Our Credit Manager can approve an application by filling out a form view of Responses. They select which record from the Credit Applications table to link the Response to the Application.
Here is where I’m running into problems. If the customer does not yet have a number assigned, the Credit Manager enters a newly assigned number as part of the form when they approve an application. I want to be able to automatically update the primary field name to replace “NEW” with the assigned customer number.
So far, I have a Look-Up field on Credit Applications that looks up the field “Assigned Customer Number” on Responses. I would love to have my primary field’s formula choose the “Assigned Customer Number” rather than the original “Customer Number” if the Assigned field is not blank. However it just does not seem to be possible without creating a circular reference… which I find incredibly frustrating.
Any advice would be highly welcomed and appreciated!
Thank you!
