New record via relationship

[JMart]

Night all,

I need some help with a script; I can figure out how to handle it.

I have two tables, table A and table B

Table A (Customers)

Field: ShipTo

Table B (Ship To)

Field: ShipTo

When a user types a ship-to location in the customer table and the record does not exist in the ship-to table, I want the record to be created automatically in the ship-to table. 

This is what I have, but I have two issues.

1 - Once executed, it locks the layout, and I have to press Show All

2 - If the location exists in the second table, it creates a blank record 

What am I doing wrong?

Any help is appreciated, or am I trying to do the impossible? 

Thanks

Rudy

 

Edited Monday at 04:01 AM by JMart

[comment]

25 minutes ago, JMart said:

When a user types a ship-to location in the customer table and the record does not exist in the ship-to table, I want the record to be created automatically in the ship-to table. 

You could use a script trigger for this, but ... The way you describe the desired process, a new record would be created also when the user made a typo in the entry. If I understand the context correctly, a better solution would be for the user to select from existing locations (say from a drop-down menu or from a card window) and if they can't find it press an 'Add New' button. And the Customers table should store only the ID of the selected location.

 

 

 

[Ocean West]

On down stream entities ( invoice / quotes / contracts ) however you would replicate all the shipping information fields and look it up and set it at the creation of the record  ( or set via a picker ) - Address data should be static for these records a snapshot in time. In the past a project I took over just referenced the address record and when the customer moved all historical invoices were now showing the new address not the address things were sent to which set off a chain of events where some paid invoices now had balance due based on the new address because of sales tax or shipping. 

 

 

[JMart]

Thank you, I've settled on using a button and card to create a new location. 

Now I am trying to create the following script, but I can't get it to work 

The idea is that once a user enters something in the field, it will check if the entry exists in the database. If it does not, a message should display, letting the user know that the location is not valid for creating a new one. 

I have the script executing on exit.

[comment]

1 hour ago, JMart said:

once a user enters something in the field, it will check if the entry exists in the database.

The simplest way to check if an entry exists is to perform a find. I see that your script does perform a find, but I do not see the stored criteria. It is also not clear where is the user's entry. I would expect to see a variable being set to the user's entry and this variable being used as the find criteria.

That being said, I still think there should be no need for such find. The process should start by user selecting from existing entries. Then, if they did not find what they need, only then they should make a new entry.

 

[JMart]

Thanks, this is my criteria.

The field in the main table, using a dropdown, if the location does not exist, the user has the option to create a new entry using a new window

Edited 7 hours ago by JMart

[comment]

Your criteria is the = operator which means you are searching for an empty field:
https://help.claris.com/en/pro-help/content/finding-empty-non-empty-fields.html