Bring down record #

Question

I have three tables:

Work Orders
Jobs
Notes

I have the following relationships established between them:Work Orders &lt; JobsWork Orders &lt; NotesJobs &lt; NotesThe notes table is embedded in both the Jobs &amp; Work Orders forms.When I input a note into the Notes table, embedded in the Work Orders form, it works as expected.It captures the Work Order record number and all of the other pertinent data points.However, when I input a note into the Notes table, embedded in the Jobs form, it captures everything except the related Work Order record number.The Job is related to a Work Order, and I need that work order record number to be brought down to the note being input into the job form (via the embedded notes table in the job form).Both the Work Order and Job are saved, and thus a recorded number is generated, prior to the note being input.Any thoughts, ideas, suggestions?------------------------------Andrewandrew.fry25@gmail.com------------------------------

johncrosland · Answer

Hi Andrew,It sounds like you need to have a Lookup Field for Work Order # in the relationship between Work Orders and Jobs. Then place a Lookup Field for that field inside of the Jobs to Notes relationship.Hope that helps!​​------------------------------John Crosland------------------------------

andrewfry · Answer

John,Thanks for the suggestion. I already had the Work Order record #&nbsp;coming down to the Job Table, but forgot to bring it down to the Notes Table from the Jobs table.However, I now have two ​​​​fields in my Notes table for basically the same thing: Work Order Record #. One via the ​relationship of Notes &amp; Work Orders and the other via the relationship of Notes &amp; Jobs.That doesn't seem ideal to me .....------------------------------Andrewandrew.fry25@gmail.com------------------------------

johncrosland · Answer

Depending on the design of your process, then you can remove that as a lookup field, and you can just populate the Related WO in Notes at creation based on the Related WO in Jobs.

This is made simpler if you keep both fields in Notes, and run an action or automation that will set the WO Number inside of Notes based on the WO Number from Job when the Related Job value inside of Notes changes.

If you want to do it based on the change inside of Job, then you can set up an automation to set the WO Number inside of Notes, based on the Related Job being equal to the Key from the Job table.

Let me know if you have any more questions or issues.

------------------------------
John Crosland
------------------------------

donlarson · Answer

Andrew,Since your Notes table has two potential parents where one can be inherited from the other, you are going to have to do some gymnastics.&nbsp; &nbsp;The cleanest thing is to use Triggers from Juiced Tech.&nbsp; &nbsp;www.juicedtech.comThey have a great Search function that lets you look through other tables, capture the Record ID# and then you can write it to your table giving you the complete relationship.Another way to tackle this is with the Look Up fields from each potential Parent and then use a Formula Numeric Field to rationalize it:[Work Order ID FN]&nbsp;if ( isnull([Related Work Order])=false,&nbsp;[Related Work Order], [Job- Related Work Order])Make sure you clear the check box on the field [Related Work Order] "Treat blank values as "0" in calculations.&nbsp; You need the reference field to be Null when it is empty and not a zero value.Now you can use a Report Link field and have it Match to the&nbsp;[Work Order ID FN]&nbsp;
This will get your forms showing the data but will not solve the inevitable follow on issues of the Notes not being directly related to both Parent Records.Click the button for Select Target and use the Formula Numeric field that you built above.&nbsp; &nbsp;Now your report will show the Notes regardless if it is the direct parent or the inherited parent.
------------------------------Don LarsonPaasporterWestlake OH------------------------------

andrewfry · Answer

John,I like this idea, but am having some issues implementing it.What I have so far for my automation is the following:

I am not getting the result I was hoping for.Any thoughts?------------------------------Andrewandrew.fry25@gmail.com------------------------------

Forum Discussion

Bring down record #

Related Content

Custom Record Titles

Using Grid Edit to Edit Multiple Records at Once

Record Details

Many parent records on a single child record

Copy Records Step - Skipping All Records