2 Replies Latest reply on Aug 22, 2011 1:40 PM by JasonRossitto

    Portal with records NOT in parent table

    JasonRossitto

      Title

      Portal with records NOT in parent table

      Post

      I've got a table of personnel with their locations which is linked from our space database software, and a table of newly hired personnel. I have a layout based on the first table, and I want to create a portal to the second table that shows only those employees who have not yet been added to the space database. That way when I import the new hires each week from HR and begin adding them to the space database I can see which ones I have left to do.

      To do this I self joined the locations table with a cartesian product, then related the hires table to the second instance of the locations table with a "not equal" operator on the username field. The parent layout is based on the first instance of the locations table.

      The problem is that the portal still shows employees who are already in the database. I tried the self join because I assumed it wasn't working because it was showing only hires that were not equal to the current record of the locations table, not any record in the locations table.

      So basically it's:

      personnelLocations::username X personnelLocations::username != hires::username

       

      I want a portal showing only items from the hires table for which hires::username does not appear in the personnelLocations table.

        • 1. Re: Portal with records NOT in parent table
          philmodjunk

          Let's rewrite your X relationship so that we have distinct occurrence names:

          PersonnelLocations::anyfield X AllPersonnelLocations::anyfield

          Now we'll pull all those usernames into a list called cAllUserNames: List ( AllPersonnelLocations::UserName )

          Add this relationship:

          PersonnelLocations::cAllUsernames  ≠ Hires::UserName

          Note: this method fails the first time you get two employees with the same name.

          • 2. Re: Portal with records NOT in parent table
            JasonRossitto

            Thanks! This worked, but only when List ( AllPersonnelLocations::UserName ) is evaluated from the context of PersonnelLocations. When List was called on the Username field from the same context as the field was specified from it didn't work. I don't understand why this is, but it's working.

            It takes ten seconds or so to process all the records the first time, but it doesn't do it again even though the calculation is unstored. What would trigger a recalc?

             

            Usernames are always unique, so I don't have to worry about dupes.