Forum Discussion

Jan-WillemSpeck's avatar
Jan-WillemSpeck
Qrew Assistant Captain
8 years ago

add record in child table in different application with reference to parent

There is most probably already a sollution described for my problem i just don't know exactly how to describe it so looking for the sollutinion is not easy either.

Here we go....

I have a table PROJECTS.
  • PROJECTS has a one-to-many relationship to MILESTONES
  • PROJECTS has to one-to-many relationship to ACTIVITIES (foreign application, where all kind of other tables have child records of an activity)

I already do have a button that adds activities to a project and that should stay this way.
On the PROJECTS-form a report is shown with all related activities and some details of these activities; status, summaries, etc

Now I would like to be able to also add activities direct from the MILESTONES table in such a way that it has a relationship to the parent record in PROJECTS and is listed in the report on the PROJECTS-form.
And ....... as a 'nice to'  it would be awsome to have the activities specific related to that MILESTONE-record from where it is created reported on the relevant MILESTONE-form.

~~ just ~like a kind of drill-down report tool~~
You see all activities related to the project, and when you drill down to milestones within th e project you see the activities related to that specific milestone.
                         ~~~~

Hope this make sense in explaning what I try to achieve.

Would this be possible?

Thanks Jan-Willem 
  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago

    Hi,

    Would it help to pass the summary field, [# of Milestones] from the Projects table to the Activities table as a lookup field? Likewise, you could pass the summary field, [# of Activities] from the Projects table to the Milestones table as a lookup field as well.

    This way you have an indirect reference to the Milestones records that have Projects in common with Activities, and also, you will have a reference to the Activities records that have Projects in common with Milestones.

    Make sense?


  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    You will want to create a relationship where each Milestone can have many activities.

    It will by default create a button for you to "Add Activity"

    You will want to modify that formula-url field to include and automatically complete the Project along with the respective milestone.

    In this example the Record ID is getting written to field id 15, and the related project it getting added to field 14.
    (This button will be on the "Milestones" table).

    URLRoot() & "db/" & [_DBID_ACTIVITIES___ACTION_ITEMS] & "?a=API_GenAddRecordForm
    &_fid_15="&URLEncode([Record ID#])&"
    &_fid_14="&URLEncode([Related Project])&"
    &z=" & Rurl()

    You will most likely need to add the portion:
    &_fid_14="&URLEncode([Related Project])&"

    So you can pre-populate the form with the related Project AND Milestone
  • Jan-WillemSpeck's avatar
    Jan-WillemSpeck
    Qrew Assistant Captain
    8 years ago
    Matthew, thanks for your explanation. This URL coding is new stuff for me. I will study it tomorrow and report back soon.

    One question already rises: the values [Record ID#]) and  [Related Project] are these fixed or will they change with every new record? If so: How do you pass the appropriate values into this formula?

    Txs JW
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      Anytime a formula references a field like [Record ID#] or [Related Project] it is dynamic.  It always updates that value based on the current record.  

      As far as the formula-url goes,  I was suggesting making the addition of the [Related Project] so that the user doesn't have to select that in the dropdown all the time.  

      Just make the user experience better.

      If you get stuck, post back. Or check out the Quick Base University courses to get your feet wet.