And what data, in what fields fo Properties do you want to use to create the contact record?
Let's assume you have a single field, Name, in the Properties table. It's simple to modify the following script to work with more fields so that will serve for this example:
First define an auto-entered serial number field in Contacts to unqiuely identify each contact. Don't rely on names as they aren't unique and people change their names. Using the serial number which will always be unique and will never change avoids issues with duplicates and changes to a contact's name.
Define a matching number field in properties and define this relationship:
Contacts::ContactID = Properties::ContactID
Now you can create a script for your button that works like this:
Set Variable [$Name ; Value: Properties::Name]
Go To Layout [Contacts (Contacts)]
Set Field [Contacts::Name ; $Name ]
Set Variable [$ID ; Contacts::ContactID]
Go To Layout [original layout]
Set field [Properties::ContactID ; $ID ]
This script copies the imported name from properties to a new name field, then brings back the new records serial number and assigns it to the property record to link them together. A portal to Properties can now be placed on the contacts layout to list all properties for that contact. If you have more/other fields in properties that contain data for your contact, modify the parts of the script shown in blue, adding more steps for each such field.
You can also format the Properties::ContactID field with a value list of existing contacts and contact names so that you can link a newly imported property to an existing contact instead of generating a new contact record.
Also, it can be very common for there to be a need to link more than one contact to a given property. This can be done with a more sophisticated table structure where a third table serves as a "join" tble between properties and contacts. Let me know if this is something you also need.
I would have a portal on the property layout so that you can see if that contact already exists in the contact table. If it does you can set a button to set the Propoerties::contactID to that value and not bother creating a new record in contacts. If it doesn't the button can copy the contact data over into a new contact record, setting both the Properties::contactID and Contacts:contactID to the same value.
Then from the contact layout you can have a portal showing a list of properties related to each contact by contactID
thanks, I'm really trying hard to figure this out. Let me revise what I'm trying to do -
Table - Lease List
Key Field - LEASELISTID
fields - True Owner Company, Lease Number, address, phone, square footage...
Table - Lease List Verified
Key Field - LEASELISTVERIFIEDID
other fields - True Owner Company, Lease Number, address, phone, square footage
Table - Contacts
Key Field - CONTACTID
other fields - Company, address, phone, square footage
I currently have a portal on the Contact layout that shows the records from the Lease List Verified table
I import data from excel into the Lease List table and modify some of the data. Once I'm done modifying the data, I want to click a button "Verify" that looks up the field Lease Number in the Lease List Verified table. If the Lease Number exists, I want it to update the record with the data in the corresponding fields from the Lease List Layout. So it goes from Lease List to Lease List Verified by clicking the button. If the Lease Number doesn't exist, I want it to create a new record in the Lease List Verified table and input the data from the Lease List table.
Do you know how to define relationships between your tables?
If you link your first two tables in a relationship that uses the Lease number, you can use that relationship to add new records to the 2nd table with a single script step.
Define this relationship:
Lease List::Lease Number = Lease List Verified::Lease Number
Doubleclick the relaitonship line and enable "Allow creation of records via this relationship" for Lease List Verified.
In each matching field in Lease List Verified, use the auto-enter tab in field options to specify that the field use the looked up value setting to copy the value of the matching field in Lease List.
Now this script step, performed from the Lease List layout, will create a new record in Lease LIst Verified if a record with the same lease number does not exist and will trigger an update if it does:
Set Field [Lease List verified::Lease Number ; Lease List::Lease Number]