Related fields between two tables

[topaznz]

Hello there Forum

I've searched high and low to answer my own question and suspect I may not be using the right keywords.

I have a Human Resources database in the making and trying to work out how to drag across the "Name" and "Personnel ID" fields into a separate table that houses Employment Contract records.

The workflow I'm trying to achieve is: Personnel Record layout > "Add Contract" - at this point the New Contract layout appears with the "Name" and "Personnel ID" dragged across from the Personnel Record layout. I'd like these two fields to be live so when the Personnel Record is updated, it is reflected in the Contract layout.

I want to create a portal in the Personnel Record that will list all added contracts with the corresponding

I've explored relationships and lookups with no success, I'm definitely missing something here.

Many thanks for any thoughts/assistance in advance.

Tim Martin

[topaznz]

Currently I am using the Copy and Paste function - so my script looks like:

* Copy [select; PERSONNEL FILE::Personnel ID]

Go to Layout ["Position Appointment Record" (PLF: Position Appointment Record)]

* New Record/Request

* Paste [select; No style; PLF: Position Appointment Record::Personnel ID]

I have a feeling this script setup will lead to trouble later. Is there another method to pull over the Personnel ID so the new Contract Record relates to the Personnel Record automatically therefore the portal set up will show all Contracts relating to that applicable Personnel member.

Hopefully this provides some clarification into what I'm trying to achieve.

Kindest regards

Tim Martin

[comment]

If you haven't done so yet, define a relationship between the Personnel and Contracts tables as:

Personnel::PersonnelID = Contracts::PersonnelID 

If you select "Allow creation of records..." on the Contracts side, you will be able to create new contracts by entering data into the first empty row of the portal.





If you prefer to create new records by script, you can:

Set Variable [ $personID ; Personnel::PersonnelID

Go to Layout [ Contracts ]

New Record/Request

Set Field [ Contracts::PersonnelID ; $personID ]

# NOW WHAT??

Note that the above creates a new EMPTY record (other than being related to the current person).

[topaznz]

Thank you for your response comment. You have been and continue to be invaluable in assisting with my projects!

Unfortunately the set up you specified matches what I currently have set up in the database.

Now that I know its not a scripting error, I'll investigate why the Personnel ID from the Record isn't populating into the Contract Record.

I'll let you know how I get on.

With thanks

Tim

[topaznz]

I think I may have cracked the problem! Well the set up below seems to be working

- The relationship between tables Personnel and Contract is now set to X instead of = (this seemed to remedy the Personnel ID field populating in Contract.

- In the portal set up I have set a filter to Personnel ID field in Contract table to match Personnel table (as I have copied to <Personnel ID> field from Personnel into the Contract layout.

It's doing the job now, but would love any feedback if you can foresee any issues down the track or perhaps I have gone about this the wrong way.

Kind regards

Tim

[comment]

I don't think that is a solution to your problem. Using the x relational operator makes ALL records in the Personnel table related to ALL records in the Contracts table. Filtering the portal does just that - it filters the portal, which is purely a layout device. Underneath, the relationship is still what it is. For example, you cannot sum up the related contracts in the Personnel table, and you cannot use Go to Related Record[] to get a found set of the current person's contracts. Not to mention it will get progressively slower as the number of records increases.

[topaznz]

I reverted the recent changes back to the original set up we started with and its functioning as it should have in the first place. Not sure what I did, but obviously I did something right this time around.

Thanks for your help comment.

With thanks

Tim

[topaznz]

How embarrassing, I had reverted back to the old Copy and Paste script (initial solution). That's why the Personnel ID field was being populated from the Personnel table suddenly. Will continue to see scour as to why Set Variable and Set Field aren't have the desired result.

Cheers

Tim

[comment]

Copy and paste are for the users, not for you. Why don't you try recreating the problem in isolation using a new file, and if you still can't figure it out, post the file here.

[topaznz]

Hi comment

Just this afternoon I cracked the case, and must tell you, I should be banned from this forum!

When it came to Set Field, I specified the target field, but never specified the calculated result where I should have put "$personnelid" minus quotes.

I really am sorry for wasting your time with this tedious oversight, but I've certainly learned something valuable.

Many thanks

Topaz

[comment]

LOL, we have all been there...