Jump to content

Related fields between two tables


topaznz
 Share

This topic is 4403 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 4403 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.