1 Reply Latest reply on Feb 21, 2009 11:29 AM by FentonJones

    Help!  Trying to link two portals



      Help!  Trying to link two portals




      I'm hoping someone out there can help me. I'm trying to create a database to track vet visits for pets.  The main database contains info about the pet. (Name, sex, type, etc), the second database I want to contain just the date for the visit to the vet.  And the third database will contain the procedures done during the visit.  My form is based on the first table, I then create a portal to display the dates of the visit.  I then create another portal to display the procedures.  My issue is, that when I click on a date in the first portal all I want to see in the second portal are procedures done on that specific date.  But it is showing all procedures for the specific pet.  How do I filter the second portal to only show the related records? 

        • 1. Re: Help!  Trying to link two portals

          The 3rd table (2nd portal, Procedures) does not know what you are clicking on in the 2nd table (1st portal, Visits). In any case, the 2nd portal's relationship is a direct line to all the Procedures of all the Visits for the Pet. So you will need a different relationship for that 2nd portal.


          The relationship would be based on a field with global storage, since you are expecting to make different choices, without actually changing any of the "real" data. The global field could be created in either the Pet or Visits table, but I'd do it in the Pet table. The contents would be the unique ID of the Visits table.


          The relationship would be from this global field to the unique ID of Procedures table. It should also include the unique ID of the Pet. Otherwise it will still show the same data if you move to another Pet record, which would be confusing. (The value of a global field is the same for all records of its table, so it would still be valid on another Pet's record; but including the Pet ID precludes that happening. BTW, you would need to have the Pet ID in the Procedures table. Let us know if you do not have it there yet.)


          You would set the global field by a button in the Visits portal, with a Commit Records step afterwards (hence a small script). You would put the fields from Procedures in that 2nd portal, based on the global relationship. They do not really need to be in a portal, as there is only 1 record matching; unless you want to hide the field labels, etc., when there is no match. You might want to clear the global when going to the layout or switching records, to clear the choice. I would put the Visit date above or next to the portal, so it was obvious what you were looking at.


          There is a clever way to save space on the layout, by putting the Procedure fields into the 2nd tab of a Tab Object, with the Visits portal in the 1st tab. Then switch between them when a choice is made (on the Visits tab), or a "return" button is clicked (on the Procedures tab). This requires naming the tabs as objects, and using Go to Object. More work, but not rocket science.*


          * I wonder what rocket scientists say? "Don't worry, it's only rocket science." :-)