Relating a Table to a Seperate Table

[NuttyMoose]

I have two tables related to this question: "Main" & "Tracking"

Main Table fields:

MainID

Tracking Table fields:

Rel_MainID

Glb_MainID

TrackingID

Relationship Used:

Main::MainID --> Tracking::Rel_MainID

Script Used:

Set Field [Tracking::Glb_mainID, Main::MainID]

Set Field [Tracking::Rel_MainID, Tracking::Glb_MainID]

What I want to do is make a script that when is run will add a related page to tracking for the main page i am on. This works very well see script used.

But I run into trouble when I already have a related page for the main page I am on...and I run this script and it adds another related page...

What I need is a script that will add a related page when one does not exists but one does exists just go to that page...

Can anyone help me

Brahim

[-Queue-]

If [isEmpty(Main::TrackingID)]

Set Field [global; MainID]

Go to Layout [Tracking]

New Record/Request

Set Field [Tracking::Rel_MainID; Main::global]

Else

Go to Related Record [show only related; Main]

End If

[NuttyMoose]

Very Nice Thank You

I will give this a try....

I will let you know how it turn out..

Brahim

[NuttyMoose]

OK I have givin this a try...

The first line "If[isEmpty(main::TrackingID]" I do not have this field on the main page...Do you mean "Tracking::Rel_MainID"?

I am assuming that this line means if on the tracking table there is a value in the Rel_mainID field do the related record lookup...if not set the fields...

Well, when I try this...it seems to work but only once...meaning when I go to another record in the main table and run this script it still brings me to the same record (on tracking table) as I did the first time...

So my only guess is that when the script looks to see if the field is empty or not it is not looking to see what record I am on...it just sees a value in the rel_mainID feild...

I need something that will look up the mainID i am on and see if there is already a record for this ID...if not set fields if so the go to related record..

Can you help more?

Brahim

[-Queue-]

Main::TrackingID would be the related TrackingID to the current record, based on the relationship you described. You didn't say what your TO was called, so I just used 'Main'.

If the related TrackingID is empty, then there are no related records to the current one, assuming TrackingID is the serial for that table.

[NuttyMoose]

ok..i under stand why you used "main"...This works great... But why is it when I run the script for any other time other than the first time...the same table (page) always opens up.

when i clear the tracking data -- and run the script it brings up the correct and new table for the ID i am on

but when i try it agin with another ID it still brings up the first ID?

Brahim

[-Queue-]

Add Commit Records/Requests after the Set Field script steps. Does it help?

[NuttyMoose]

no sorry it stills brings up the first trackingID...not the ID i am on.

All the script is doing is if there is not a value in rel_mainID then add a new page...this works great if there is a fresh start to the tracking table...meaning the first entry ever...

But when I have added one before all its seeing is there is a value in the rel_mainid so goto related record...which is not right...i need it to look specify for the id i am on...then if there is not one add if there is one goto

Brahim

[-Queue-]

Are you specifying Show only related records in the Go to Related Record step? If the records are uniquely related, then it should only show one record. If you don't have that option checked, then it will show all related records, i.e. all records in the second table that are related to records in the first table.

[NuttyMoose]

I am specifiying show related records...

I think the mess up is go to related record....

The script you showed me says (I think) if the rel_mainid is not empty then go to related file...

I did a test... I deleted all of the records in "tracking" and then I reset the serial numbering for the trackingID...

Then i went to MainID 12 (name: Bob Smith)...and ran the script...it add the a new record like I wanted and even related it to MainID 12 (bob Smith)...

Now when I return to the Main table and run the same script again for the same MainID...it goes right back...Perfect

Now I go back to The main table and go to MainID 50 (Rob Johnson)...I run the script it goes stright to MainID12 (Bob Smith) not Rob Johnson...Why?

Help?

Brahim

[-Queue-]

Can you attach the file so I can look at it?

[NuttyMoose]

Well, I wish I could...The company says there is private information on it...so I can not...But...I will recap my script for you...I think you will see the problem for me at least I hope you can.

Tables:

Main

Tracking

Relationship:

Main::RecordID --> Tracking::Glb_MainID

Script:

If [isEmpty (Tracking::Glb_MainID)]

SetField [Tracking::Glb_MainID; Main::RecordID]

Go to Layout ["Tracking" Rel_MainID; Tracking::Glb_MainID]

Else

Go to related record [show only related records; From Table: "Tracking" using layout "Tracking" (Tracking)]

End If

Does this look right...the problem might be with the relationship that I have???

Brahim

[NuttyMoose]

WOW....I got it...it was the relationship.... You were correct... which I think I always new that you were. Thank you...

Brahim