8 Replies Latest reply on Jun 24, 2015 4:40 PM by philmodjunk

    Custom Function not available

    MikeWile

      Title

      Custom Function not available

      Post

      I added a custom function that removes duplicate names from a list of names that are stored in a field. To test this out, I created a button that would run the script on the field and remove the duplicate names.

      It worked great so I wanted to add the custom function to a calc on the field. It gives me the message that the function is not found. If I click, in the calc window, show custom functions, it's not listed.

      If I go to File/Manage/Custom Functions, it's listed. Any ideas on why the function isn't available in the calc window?

      For now I can use the button approach but there are 5000 records. That particular field could hold hold an average of 500 names when the pared-down list is only 4 names.

        • 1. Re: Custom Function not available
          philmodjunk

          I have no idea why it wouldn't appear. What name did you give it?

          Putting an average of 500 names into a single text field seems a less than optimum design. I'd definitely consider using a related table for this data instead.

          • 2. Re: Custom Function not available
            MichaelWile

            I named it "RemoveDuplicates"

            • 3. Re: Custom Function not available
              MichaelWile

              Putting an average of 500 names into a single text field seems a less than optimum design. I'd definitely consider using a related table for this data instead.

              That's why I'm using the custom function. The script creates the list and the function reduces it down to 4 names and places the names into the field. 

              I'd like to have the calculation done automatically instead of clicking the button to update the list.

              • 4. Re: Custom Function not available
                philmodjunk

                Placing more than one name into a field seems a less than optimum design let alone the original set up of 500 names. In most situations, I'd put each name into a different related record.

                As a workaround, you could use a Looping script that does a batch update of your entire set of records.

                Don't see why that custom function won't appear in the list. Try adding this custom function to a brand new file and see if it appears in the function list in that file. If so, you might need to run a recover on your original file.

                • 5. Re: Custom Function not available
                  MichaelWile

                  Another approach would be to put a portal in the layout. Right now it would list 500 elements. These 500 elements would consist of only 4 names, one associated with each record.

                  Is there an easy way to filter the portal to eliminate duplicate names?

                  • 6. Re: Custom Function not available
                    philmodjunk

                    I'm not sure if it would fit your definition of "easy", but it is possible using an added occurrence of the related table of names in your relationship graph or by replacing the portal with a large calculation field using ExecuteSQL with the DISTINCT keywork. But it would be better to prevent the creation of the duplicates in the first place. There are a number of options that can do that. The simplest to implement is a unique values validation on a field in the portal table.

                    • 7. Re: Custom Function not available
                      MichaelWile

                      Here is what this solution is doing. There are 2 tables; Book and Art.

                      There are about 5000 different books in the Book table. Each book contains a certain amount of art. In this case, 500 pieces.

                      This art right now is being drawn by only 4 different art sources. 

                      When I go to the record for a particular book, I'd like to see a list of the artists that are working on that particular book. 

                      So, if I create a portal, it will list the first piece as artist 1, the second as artist 1, the third as artist 4, and so on for 500 different pieces.

                      I will look into your suggestion of ExecuteSQL.

                      • 8. Re: Custom Function not available
                        philmodjunk

                        Then you should have these relationships:

                        Books----<Art>----Artists

                        Books::__pkBookID = Art::_fkBookID
                        Artists::__pkArtistID = Art::_fkArtistID

                        A portal to Artists on the Books layout will list all artists without duplication.

                        This assumes that each artwork is only used in one book. I've seen plenty of cases where the same art is used in more than one book, so you may need even one more table between art and books so that the same art can be linked to more than one book.