3 Replies Latest reply on Aug 21, 2014 9:00 AM by philmodjunk

    Scripting Portal Records

    DanPhillips

      Title

      Scripting Portal Records

      Post

           Hi All,

           I'm trying to get my head around a little problem that I'm sure is simple but my Googling and book reading hasn't pointed me in the right direction at all. So I'm hoping someone can help me out here who knows how to script for portals.

            

           The issue is I have a customer table (contracts) that has a special key for each customer in format of ABC1234. 

           I have another "lead" table that has a field for "referred by" which you would put the key of the customer who referred this lead. So all I have to do to find any lead that was referred by ABC1234 is search the lead table for referred by ABC1234. That works fine. 

           Now what I want to do is setup a portal on the contract layout so when I'm viewing ABC1234, I can see data from all the referral leads where referred by is ABC1234. Without actually having a relationship (the key is already tied to the lead table in another way) I'm finding this a bit out of my skill level to do. Does anyone know how I could write a script that will perform a find and then populate the portal with the results? 

           Thank you in advance!

           -Dan

        • 1. Re: Scripting Portal Records
          philmodjunk

               First, an ID formatted like this is not the ideal primary key. An auto-entered serial number would be a better choice.

               But sticking with what you have, you can set up the relationship you need by creating a new Tutorial: What are Table Occurrences? of Leads and now you can use your ID field to link records to this new table occurrence without this interfering with existing relationships. When you add your portal to your layout, select this new table occurrence in the "Show Related Records From" drop down.

               To create a duplicate table occurrence, Open Manage | Database | Relationships, Click Leads to select it and then click the duplicate button (two green plus signs). This creates a new "box" on this tab for referring to records in Leads but does not create a new table over on your Tables tab.

          • 2. Re: Scripting Portal Records
            DanPhillips

                 Thanks a lot PhilModJunk!!! I wish it was a serial number like any other PK but this is not exactly the PK just a kind of custom serial. The actual PK of the records is an auto-enter serial but that's not how the referrals are linked sadly. 

            • 3. Re: Scripting Portal Records
              philmodjunk

                   Then you should update the design of your database to link by an auto-entered serial number field. Define such a field in the parent table, use Replace Field Contents with the serial number option to give all your records a serial number value in the parent record, then add a number field in the child table and use Replace Field Contents with the calculation option to copy over the serial number from the related parent records.

                   Then update your relationship to match by the new ID fields and delete the old ID field from the child table.

                   If you still need the original ID field for reporting purposes and to otherwise keep your users happy, keep this ID in your parent table so that you can make it appear where needed on reports, labels, search screens, etc...

                   PS. I realize that this is not a change to make lightly, you'll want to carefully evaluate and identify all changes to layouts, scripts etc needed in order to make this work and may even need to "rehearse" this changeover on back up copies of the database before doing it for real. But if you can pull it off, you will have made a long term improvement toward better protecting the data integrity of your system.