11 Replies Latest reply on Apr 15, 2014 4:11 AM by duncanbaker

    Sorting a portal issue

    duncanbaker

      Hey folks

       

      Been trying to figure this out for a while and struggling. In simple terms, here's the scenario:

      Contacts

      Orgs

      JoinTable - with a field indicating if the org is the primary org

      There can be multiple joins records for contacts and orgs (for other reasons not worth going into here), so on a contact layout the portal to show orgs is based on con>join>orgs. This works fine and shows just one instance of the org name no matter how many joins there are.

      But, if I want to sort the portal so that the primary org is at the top and the rest are a-z by org name, how to do this? I need a field in the org table that indicates if the org is the primary org for the contact being looked at. I've tried all manner of calcs and relationships and while I can get a field in contacts to show me which is the primary org, I can't get it to work by making a field in orgs.

       

      Any thoughts?

       

      Thanks

        • 1. Re: Sorting a portal issue
          PSI

          Hi Duncan,

           

          Add the Primary Org flag to the join table and add a field for the org name, which can be a lookup when selected.

           

          John Morina

          • 2. Re: Sorting a portal issue
            duncanbaker

            Sorry I may not be getting this. I have a field in the join table already for the primary flag - this is working fine. My portal is based on the relationship of con>join>org so I'm not looking directly at join table records as if I did I'd see multiple instances of the same org name (as there can be more than one join record between con and org). I'm not sure how adding a field for the Org name in the join table helps as I think I need a sort field in the Org table?

            • 3. Re: Sorting a portal issue
              PSI

              Well you can't sort on the org name in the org table so adding it to the join table will allow you to sort by it.

               

              as far as multiple entries of the same org...I don't understand why a given contact would have an org listed more than once?

               

              John

              • 4. Re: Sorting a portal issue
                erolst

                See if this helps you. Look into the join table and filter out duplicate entries by a proxy flag; sort by a calculated primary flag descending, name ascending.

                 

                Note that the primary orgID is an attribute of a person, not a join table record; consequently, it should be stored with the contact, then – as in the example - it can be calculated in the join table (though normally simply displaying it would be sufficient).

                • 5. Re: Sorting a portal issue
                  erolst

                  PSI wrote:

                  Well you can't sort on the org name in the org table so adding it to the join table will allow you to sort by it.

                  That's not necessary; switch the portal temporarily to another TO (preferably one that makes sense in the relationship chain and is located “behind” the portal TO) and add a field from that TO to the sort order, then switch back to the original portal TO.

                  • 6. Re: Sorting a portal issue
                    duncanbaker

                    This looks to be a solution. It'll require a bunch of reworking but probably the right way to do it. Thank you for taking the time and the demo file is incredibly useful. I'll pick it apart and recreate in my solution. Much appreciated.

                    • 7. Re: Sorting a portal issue
                      duncanbaker

                      I'm picking it apart to understand what's going on. I see you have a MIN formula in the field cIsProxy. I'm using UUIDs as primary keys and I tested it and it doesn't work with those. I changed _kp_orgPplID to a text field and Get (UUID). I also tried changing cIsProxy to a text calc. I'd like to keep the UUID strategy if possible. Any options here?

                      • 8. Re: Sorting a portal issue
                        erolst

                        duncanbaker wrote:

                        I see you have a MIN formula in the field cIsProxy. I'm using UUIDs as primary keys and I tested it and it doesn't work with those.

                        Funny you should say that; works here … since Code ( 2 ) < Code ( "B" ) < Code ( "b" ).

                        UUID_Min_Max.png

                        duncanbaker wrote:

                        I'm picking it apart to understand what's going on.

                        In a nutshell: you need a method to designate one single record per org/contact combo as the proxy for your portal display; using a numerical value and a statistic function is (I think) the easiest way (you could as well use Max() …).

                         

                        You want to use a guaranteed non-empty field for the comparison; if for some reason you cannot get your UUIDs to work, an auto-enter creation timestamp will do as well (if you don't have one, you should …).

                        duncanbaker wrote:

                        I also tried changing cIsProxy to a text calc.

                        Not a good idea; leave it as type number to return a result of 1 or 0 (the record is Min/Max – i.e. proxy – or it's not), so you can use it in the portal filter calculation without further ado as a Boolean expression ( Case ( JoinTable::cIsProxy … etc.)

                         

                        Don't confuse the data type you're performing the comparison on with the data type of the field that holds the result of that comparison.

                        • 9. Re: Sorting a portal issue
                          duncanbaker

                          Got it. I continued playing around and found it does work if you use a UUID and leave the field type as a number but I believe UUIDs are text really so not sure I should do that. I'll try the timestamp option as I do have created and modified fields. Thanks again for taking the time.

                          • 10. Re: Sorting a portal issue
                            erolst

                            duncanbaker wrote:

                            [I] found it does work if you use a UUID and leave the field type as a number but I believe UUIDs are text really so not sure I should do that.

                             

                            Right; but you could go the other route and use

                             

                            GetAsNumber ( UUID ) = Max ( selfJoin::UUID ),

                             

                            which works. But since you have timestamps anyway, this is more of academic interest (but might come handy another time).

                            • 11. Re: Sorting a portal issue
                              duncanbaker

                              Understood. Good point. I'm going the timestamp route which is working fine on this end. Thanks again for the help.