1 2 Previous Next 15 Replies Latest reply on Sep 8, 2014 10:22 AM by jlamprecht

    Removing Duplicates from Portals

    mklimow

      Hello,

      What I am doing is creating a portal that stores what materials each supplier uses. Each supplier we have creates parts, and each part is created with a certain material. to link between part and material we used a direct link, but between supplier and part, we had to use a join table to hold the instances of the link, since they can have multiple suppliers, as well as we wanted a history. So i used the join table between part and supplier for the portal and am showing the material number that is linked to the part in the portal, but since different parts can use that material we end up with duplicates in the portal.

      I am creating a portal that keeps on returning duplicates for each value. I am trying to find a way to remove the duplicates so it only shows each individual value.

       

      I found this response on the web, but the discussion was closed a while ago. It looks like what I need to do for the portal, but I am unable to figure out how to put it together.

       

      The Response is:

       

      First create a calulated field that determines if the value has duplicates in other records. This requires duplicating the TO and then...

      Then create a calculated field that will show the id field value only for the lowest numbered id based on the duplicated calculated field above.

      Change your original link to the second field and this should exclude all of the duplicates since they will have no value in that field.

      I will try this idea in my file and find out what exactly is needed.

       

      If you can explain this response for find a better way for me to accomplish this, it would be a big help.

       

      Thanks

        • 2. Re: Removing Duplicates from Portals
          mklimow

          I have tried a few of those options you suggested, but they are not working, I have other filters i need to apply as well, for instance i need it to filter by whether the supplier makes the part or just purchases it, whether the supplier is active or not, and if the Material number is blank inside of the part detail or not. I have tried the field and selfjoin that you have created in that example, but it shows 0s for all of the the values in the portal, after I filter by those other results.

          • 3. Re: Removing Duplicates from Portals
            erolst

            Why don't you post a sample file that exemplifies your relationships?

             

            Creating and applying a filter shouldn't be too complicated, but creating a file is tedious …

             

            mklimow wrote:

            but it shows 0s for all of the the values in the portal, after I filter by those other results.

             

            The result of that calculation field should in no way be dependent on other (or any) filters you apply to the portal. Something else must be off.

            • 4. Re: Removing Duplicates from Portals
              mklimow

              Here is a Sample of what it kind of looks like. Let me know if you notice what is wrong.

               

              Thanks

              • 5. Re: Removing Duplicates from Portals
                erolst

                1. The Material Number in the join table is a calculation that references the material number in Parts – and thus is par force unstored and non-indexable. Therefore, you cannot use that field on the “target” side of a relationship, and your self-join fails. Use an auto-enter calc instead.

                 

                2. If you need additional (inclusion/exclusion) criteria, you must encode them in the relationship, not in the calculation; without that, the calculation will look for the Min() or Max() among all parts the same MTs, and what it finds isn't necessarily an active, non-purchased part.

                 

                3. You could use an SQL calculation similar to the one I suggested in the other thread, and forget about calcs and relationships.

                 

                See attached.

                • 6. Re: Removing Duplicates from Portals
                  mklimow

                  The Problem with autoenter, is that we have the links created already before we have the material added. We add the materials as we determine them, and the materials can change for each part as well. would SQL work if I use the calculation I had?

                  • 7. Re: Removing Duplicates from Portals
                    erolst

                    mklimow wrote:

                    would SQL work if I use the calculation I had?

                     

                    Yes, it would. But you could as well use a JOIN to the Parts table instead.

                    • 8. Re: Removing Duplicates from Portals
                      mklimow

                      I have attempted to use those, but for some reason I am still not getting any values in the portal.

                      • 9. Re: Removing Duplicates from Portals
                        erolst

                        That's too vague to make any suggestions, except to be aware that SQL reserved words and field names with leading underscores need to be double-quoted (which is why I renamed some fields for the sample file).

                         

                        SQL has some peculiarities, and if you can't spend the time to dig a bit deeper into the subject, maybe this isn't the right approach for you.

                         

                        On the other hand, this de-duplicating via filter is is all very interesting and can be useful, but I think in your particular case there's an easier solution. Filter out the non-suitable SupplierPart combinations and use the rest to look through a new relationship chain into Material:

                         

                        Create a calc field in SupplierParts as

                         

                        supplierID_applicable = Case ( not ( Status = "Inactive" or Type = "Purchased" ) ; ID_Supplier )

                         

                        Create a new relationship from Suppliers to SupplierParts_applicable as

                         

                        Supplier::ID = SupplierParts_applicable::supplierID_applicable

                         

                        Now “look through” this chain of new TOs:

                         

                        Supplier --< SupplierPart_applicable --< Part_applicable --< Material_applicable

                        • 10. Re: Removing Duplicates from Portals
                          mklimow

                          What would you consider a leading underscore though? I know of some of the reserved words, and I was under the impression that they had been avoided in here. Also the database is in current use, and changing these fields would cause way to much of an issue, if calculations cannot be used. The tables I added are used through atleast another 10 tables in the database, so the fields cannot be changed without affecting the current setup of the database.

                           

                          I can try the other solution, but I wanted to get the filter to work, instead of creating more TOs to be used.

                          • 11. Re: Removing Duplicates from Portals
                            erolst

                            I didn't say that leading underscores or reserved words are the cause for anything in your calculation, just to be aware of them as potential pitfalls. Also, you don't have to rename such fields to be able to use them with SQL, you can simply put them in quotes …

                             

                            If you ask for assistance troubleshooting your calculation, why don't you post it?

                            • 12. Re: Removing Duplicates from Portals
                              mklimow

                              I am tried to adjust the SQL you recommended, and when I change the values to what I have in the database, it shows no records at all. It is removing all of the records from the portal through the filter, instead of the blank and incorrect ones.

                               

                              Let (

                                IDList =

                                  ExecuteSQL ( "

                                    SELECT MIN ( SP.ID )

                                    FROM SUPPLIERPART SP

                                    JOIN PART P

                                    ON SP.ID_Part = P.ID

                                    WHERE

                                      SP.Status <> ? AND

                                      SP.Type <> ? AND

                                      SP.ID_Supplier = ?

                                    GROUP BY P.MT_number

                                    " ; "" ; "" ; "Inactive" ; "Purchased" ; SUPPLIER::ID

                                  ) ;

                                not IsEmpty ( FilterValues ( SUPPLIERPART::ID ; IDList ) )

                              )

                               

                              I have tried removing certain lines from the SQL and I have been getting incorrect information then. It shows a record when I remove the join and group by from the SQL, but it then shows a blank record only, not all of them just only 1 blank record. I am really confused on why it would show just one record in the portal after the portal.

                              • 13. Re: Removing Duplicates from Portals
                                erolst

                                In such a case I find it best to totally deconstruct the formula and test its components. Doing that with yours showed that the innocuous looking statement

                                 

                                SELECT ID from Part

                                 

                                returns ? – because for some reason 'Part' isn't accepted. Wrap it into double-quotes, i.e. JOIN \"Part\" P … et voilà!

                                 

                                Don't know what's wrong with 'Part', though …

                                • 14. Re: Removing Duplicates from Portals
                                  mklimow

                                  Thanks, that helped a lot, I wouldn't have thought to check the quotes for Part. I am still not used to the single and double quote for SQL. Do you have a recommendation of where to go to look into the single and double quotes? If so it would be helpful.

                                   

                                  Thanks

                                  1 2 Previous Next