Discussions

 View Only
  • 1.  Bring down record #

    Posted 12-23-2019 11:22
    I have three tables:
    • Work Orders
    • Jobs
    • Notes
    I have the following relationships established between them:
    Work Orders < Jobs
    Work Orders < Notes
    Jobs < Notes

    The notes table is embedded in both the Jobs & 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?

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------


  • 2.  RE: Bring down record #

    Posted 12-23-2019 11:31
    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
    ------------------------------



  • 3.  RE: Bring down record #

    Posted 12-23-2019 12:24
    Edited by Andrew Fry 12-23-2019 12:25
    John,

    Thanks for the suggestion. I already had the Work Order record # 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 & Work Orders and the other via the relationship of Notes & Jobs.

    That doesn't seem ideal to me .....

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------



  • 4.  RE: Bring down record #

    Posted 12-23-2019 13:09
    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
    ------------------------------



  • 5.  RE: Bring down record #

    Posted 12-27-2019 16:56
    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?

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------



  • 6.  RE: Bring down record #

    Posted 12-27-2019 17:09
    I think that I just fixed my issue.

    I had to change the action part of it to capture the correct Note # (unique ID) rather​ than the Job-Work Order ID#. It seems to be working now!!

    Thanks again for the suggestion.

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------



  • 7.  RE: Bring down record #

    Posted 12-27-2019 22:33
    Hi Andrew,
    Glad that you're happy with the solution.

    I noticed a couple of things. Your automation only runs when a record is added to the Notes Table. So, is it not possible for a user to add or change the linked job inside of an existing record? Also, in your current set up. You might be overwriting the user inputted WO Number linked to that Note (which might be different than the WO Number from the Job record). Unless your form has rules that says that you can't select a Job Number that isn't linked to the WO Number.

    To allow for this operation to take place when the user changes the record, then also run the check on modfies a record as well.
    To prevent overwriting data, then I would recommend checking the value of WO Number inside of the criteria (possibly create another automation that will run first)

    Hope that helps some more,
    John

    ------------------------------
    John Crosland
    Software Developer
    Inglett & Stubbs
    Mableton GA
    ------------------------------



  • 8.  RE: Bring down record #

    Posted 12-29-2019 15:35
    John,

    Thanks for the suggestions. The Job and WO numbers are the unique record numbers when a record is created. I just renamed them from "Record ID#" to "Job ID#" (alias of "Job#")​, "Work Order ID# (alias of "WO#"), & "Note ID#" (alias of "Note#").

    The user will not be inputting those numbers in any situation possible as they are generated at time of record creation.

    As far as the automation only running when a record is added, yes, that is what I need it to be. Once the note has been input.

    The only time that I might possible need to capture a modification to made to a Note, being re-assigned to a different job, is if the note was erroneously input for an incorrect job.

    But in those cases, the user is not able to select which is the correct job, by design.

    But your suggestions have given me some food for thought on how I might better the process, workflow, and automations.

    Thanks a ton John!!

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------



  • 9.  RE: Bring down record #

    Posted 12-23-2019 23:34
    Edited by Don Larson 12-24-2019 07:48
    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.   

    The cleanest thing is to use Triggers from Juiced Tech.   www.juicedtech.com

    They 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] 
    if ( isnull([Related Work Order])=false, [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.  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 [Work Order ID FN] 



    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.   

    Now your report will show the Notes regardless if it is the direct parent or the inherited parent.




    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 10.  RE: Bring down record #

    Posted 12-27-2019 17:10
    Don,

    Thanks for the suggestion. I will have to take a look at the services that Juiced offers.

    I liked your idea of using a formula to rationalize from which source I am grabbing the ID. I will have to remember that for a different scenario!

    ------------------------------
    Andrew
    andrew.fry25@gmail.com
    ------------------------------