Best Practice for Records Check before New Record Committed?
I have a "property" table listing every unique property coming through the company. I also have a "deal invoice" TO, which is related to the Property TO.
My goal is to have the user input a Property on the "Deal invoice" layout, then check to see if there is a record of that inputted property in the "Property" table; if not, then add such user input as a new property then set field "DealInvoice::PropertyID" as the new property; if record is Found, then use such found record as the value to setfield "DealInvoice::PropertyID".
Im looking for the "Best Practices" approach for the above (which I believe will be a script). I can probably string together something (like setting a local variable using a newly created field for the user to type a Property name etc...also, I've got validation set up, but I think validation only gives me an answer (the input is valid or is not), but not sure if I can use validation results as a variable in a script?. Also, not sure how best to get the Users input into the script?
Anyhow, I'd like to do this in the most effecient manner. Any suggestions?