5 Replies Latest reply on Oct 25, 2013 2:12 PM by philmodjunk

    Relationship at Time and After



      Relationship at Time and After


           I would like to create a way to check off (mark) items in a portal, this will then be assets included with the creation of a task (via a script) for the record that holds the portal. The task needs to have these assets. If I just use a relationship and let the checked value list have barring on my Table Occurrence (obviously) the relationship will be invalidated when I reset the checkbox. Is there a way to have it be there at a time and then stay in place after the Task is created with the assets? If the above is not completely clear (sometimes it is difficult to explain what you are looking for with these tools), what I mean generally; Is it possible to have a relationship via Table Occurrence be valid only at only at the time of record creation, and then hold the result? Or any other way?

        • 1. Re: Relationship at Time and After

               I'm sorry but that's not really clear. You may want to try describing what you want to do using an actual example from the situation you are trying to support with a database solution.

               If you need to specify one list of values for the same parent record at one point in time and then need to specify a different list of values at a different point in time without losing the original list specified, it would appear that you need a set of related records linked to the parent record where you specify that list so that you can create a new record at each point in time that you need to specify a new list without losing preceding lists of values.

          • 2. Re: Relationship at Time and After
            1.           My record has a portal. In this portal I have documents. The number could be any for here let’s say 10 documents.
            3.           I need to create a Task (a Task being a table also something I do in the database) for the record. I now want the documents (in portal) to be included with the created Task.
            5.           I check a box to signify that I want the records to be included, let’s say only 3 of the 10 records.
            7.           The relationship (I created it to use the checked data)now sees the checked records
            9.           So now I have the checked documents showing in the Task because of the relationship.  How do I keep them there when I reset the checked box to none?

                 I am testing some way to set an ID after they are in place. Is it possible to have a relationship only at a time?

            • 3. Re: Relationship at Time and After

                   I think I get the picture, but let's be sure:


                        My record has a portal.

                   I'll refer to that record as the "parent record" here. You want to assign a group of documents to a specific task. If you then select a different task that is also associated with the same parent record, you want to be able to see and list a different group of documents.

                   That it?

              • 4. Re: Relationship at Time and After

                     Yes! I can easily see what I want via a relationship, but the criteria that makes it so changes after it makes it so. In this case the check box. If I put a check box as Table Occurrence criteria I see the check documents in Tasks. I want them to stick around after. After being when they are not checked in the portal. Because of course I have to un-check it on the record side now. (I am currently working on a script/way to un-check it also).

                     Tell me can a script loop through portal rows and check conditions? If so this may be the way to go. I guess a script could just set a task id on documents if something is so, and then I don't need any weird relationships.

                • 5. Re: Relationship at Time and After

                       Ok, you need another table so that you can link selected documents to a selected task. I am assuming that each document and each task only links to one parent record. That may not be the case, you may have some many to many relationships and join tables, but I think you can get the idea on how to do this even if that is the case though your relationships will be more complex.

                       What you seem to have now:


                       Parent::__pkParentID = Documents::_fkParentID
                       Parent::__pkParentID = Tasks::_fkParentID

                       You can add a button in the portal row to task to select a given task. This can look/act like a check box or you can set up all the fields in the portal row as a button and use conditional formatting to highlight it, but you'll want some method to visually indicate which task is the current task. To this basic setup, we can add more TO's and relationships:


                       Document_Task is a new join table and Documents|Task is a new occurrence of Documents.

                       Parent::_fkSelectedTaskID = Tasks::__pkTaskID
                       Tasks::__pkTaskID = Document_Task::_fkTaskID
                       Documents|Task::__pkDocumentID = Document_Task::_fkDocumentID

                       The button in the Tasks portal would do this:

                       Set Field [Parent::_fkSelectedTaskID ; Tasks::__pkTaskID ]

                       Now you can add a button in the Documents portal row to do this:

                       IF [ Parent::_fkSelectedTaskID // field is not empty ]
                           Set Variable [$DocID ; value: Documents::__pkDocumentID ]
                           Set Variable [$TaskID ; value: Parent::_fkSelectedTaskID ]
                           Freeze window
                           IF [ Not IsEmpty ( FIlterValues ( List ( Document_Task::_fkDocumentID ) ; $DocID ) // Document already listed ]
                               #Delete document link
                               Go To layout: "Document_Task" (Document_Task) ]
                               Enter Find Mode []
                               Set Field [Document_Task::_fkDocumentID ; $DocID ]
                               Set Field [Document_Task::_fkTaskID ; $TaskID ]
                               Perform Find[]
                               Delete Record
                               #add document link
                               Go To layout: "Document_Task" (Document_Task) ]
                               New Record/Request
                               Set Field [Document_Task::_fkDocumentID ; $DocID ]
                               Set Field [Document_Task::_fkTaskID ; $TaskID ]
                           End If
                           Go to Layout [original layout]
                       End IF

                       While a check box list look and feel can be done, it'll take more design work to do. At this point, add a portal to Document_Task to your layout and add fields from Documents|Task to the portal row and you should be able to click a task in the portal to see the documents assigned for that task and be able to click a button in the documents portal to add/remove documents from that list.