12 Replies Latest reply on Dec 6, 2011 7:47 PM by disabled_JustinClose

    List Unique Values



      List Unique Values




      If i have a table with all kinds of item numbers listed, is there a way to make another table list only the unique values


      So say table A shows ID1, ID2, ID3, ID3, ID2, ID1

      Then table B will result ID1, ID2, ID3 only instead of showing all of them?

        • 1. Re: List Unique Values

          Open manage | Database | tables and double click the filed definition for this field in Table B. Set a Unique Values, validate always validation rule on this field.

          Now use Import records to import all the data from Table A into Table B. The unique values validation rule will block the import of any records that will introduce a duplicate value on this field so it filters out all the duplicates for you during the import.

          • 2. Re: List Unique Values

            Thanks Phil however i do not think that this is what i was looking for.

            These item numbers are constantly changing. We get about 50 orders a day of hte random numbers so i was hoping that i could some sort of constrant on the current found set to display in a table or something like that. I'd rather not have to import the records daily.

            • 3. Re: List Unique Values

              Then you don't have or need a second table, just a specific found set of unique values. There are several approaches that can be taken, but which is best depends on how you will use this list of unique values.

              How do you intend to use this "found set" of unique values?

              • 4. Re: List Unique Values

                I am still doing searching, but stumbled across this posting while doing my research and figured I would post.


                I have a similar question; I am trying to constrain a found set to only unique values in a certain field.  (The original found set has many records, each record has the name of a person who is assigned to that record.  Multiple records could be assigned to the same person.)  I am trying to constrain this found set to include only a list of the unique names of the people assigned to records.  (There is also a flag for whether or not the records has been 'finished'; that is, it will track if something has newly been assigned to someone and it is unfinished.)  This will all be done in a script, so a sub-summary doesn't seem plausible. 


                I am going to be sending an email to these folks, thus "ASSIGNED TO = 'not blank' " and "DONE = no", to let them know that they are assigned; so I only need their name once.  (I was then planning on running a loop to collect all their names in an array variable...and then unloop it back to the "TO:" field of the email in some way.  Haven't gotten that far yet.)


                --  Justin


                • 5. Re: List Unique Values

                  Just realized one method that I could use in my particular situation:  I can sort the records by 'assignee name' and then run the loop, but only add to the array if the value is different than the previous value.  That gets me what I need I think; but it seems like there would be a more efficient way of doing it.

                  --  Justin


                  • 6. Re: List Unique Values

                    Hi Justin,

                    When you have the need to find unique records, it works best to handle it relationally, i.e. it indicates that you probably are missing a critical table in your solution.  The fact that you need unique 'assignee name' indicates that there should be a table Assignees.  This Assignee table can be created by setting its primary key to validation 'unique' and importing the assignee from Assignments into it.  Now your Assignees table is your unique list.

                    This new table would then hold the person's email address, name as so on.  The tables should also be properly related so this means that Assignees table should have an AssigneeID - a unique, FM-generated, auto-enter serial you set on the field's options under auto-enter tab.  Then your Assignments will hold the AssigneeID as well.  In graph, set as:  Assignments::AssigneeID = Assignees::AssigneeID.

                    With that in place, you have established a proper, solid relational structure.  You can now take your 'Done' field from assignments and place it on your Assignees table.  If you want to find all Assignees who have open assignments, perform a find in this field for = in this field (Done is empty). 


                    • 7. Re: List Unique Values

                      I am not sure how this is a better solution, just for what seems like a simple function of finding unique fields in a found set.  This would clutter up the relationship graph and create a whole new table of stuff. In my particular problem, too, there are multiple assignments that can be made across multiple different projects.  So it doesn't seem that having a single 'Done' field would work. 


                      Here is what I have implemented up to this point:


                      [...do a FIND for the assigned, but unfinished, records...]

                      Sort Records [ Specified Sort Order: Parts::AssignedTo; ascending ][ Restore ]
                      Go to Record/Request/Page[ First ]
                      Set Variable [ $i; Value:1 ]
                          If [ $i = 1 ]
                              Set Variable [ $Assign[$i]; Value:Parts::AssignedTo ]
                              If [ Parts::AssignedTo <> $Assign[$i] ]
                                  Set Variable [ $i; Value:$i + 1 ]
                                  Set Variable [ $Assign[$i]; Value:Parts::AssignedTo ]
                              End If
                          End If
                          Go to Record/Request/Page[ Next; Exit after last ]
                      End Loop
                      Set Variable [ $maxI; Value:$i ]


                      I haven't tested it yet, though.  :)  Got to fill in a few more bits before it will work.


                      --  Justin

                      • 8. Re: List Unique Values

                        Hmmm...can you use a loop inside a calc field somehow?  Running into questions about the creation of the body text; I want to create a single email that goes to everyone who has unfinished work, but inside the body has separate lines mentioning how many they have to do with their names, too.  I have this information stored in array variables already, just need to spit it out somehow.  Could create a text field outside the Send Mail function, I suppose, to build the body text ahead of time.


                        • 9. Re: List Unique Values

                          You might try using the List Function to produce a simple "table" of text in the body of your plain text email.

                          • 10. Re: List Unique Values

                            That could work.  How would I insert text into the middle of that, though?  I.e.

                               Bob:  2 parts to fix

                               Sue:  3 parts to fix

                            The variable has the names, another variable the numbers, and then the fixed text...   :)


                            I will play around with it.

                            • 11. Re: List Unique Values

                              You set up a calculation field that produces one line of your "table" in the related table, then use the List function to create a list of these entries.

                              • 12. Re: List Unique Values

                                Well, I went with doing everything in a script.  A couple of loops (one to collect data, the other to build the variables) did the trick.  I decided to go with building up the various parts (email To:, Body:, Subject:) because those could be used in either mode of sending the email from FM.  And I could build the lines from parts, and then put the parts together rather easily.


                                The loop I posted above wasn't very good, apparently; discovered that after doing some actual testing.  So the one I ended up with is rather different.  My loop to find unique values simply uses a sort followed by filling the array with the names; the loop checks the current record against the prior one to see if the names are the same.  If they aren't, it starts a new array entry and counter.


                                    #   Yeah, just redefining the same variable to same value, but it works in the end.
                                    Set Variable [ $Eds[$i]; Value:Proj::AssignedTo ]
                                    Set Variable [ $Count[$i]; Value:$Count[$i] + 1 ]
                                    Go to Record/Request/Page [ Next; Exit after last ]
                                    #    Will be checking for equality to the prior record to determine if it is a new name.
                                    If [ Proj::AssignedTo <> $Eds[$i] ]
                                        #   Increment $i and then load up next round of variables.
                                        Set Variable [ $i; Value:$i + 1 ]
                                        #   This count needs to start at 0 for each assignee.
                                        Set Variable [ $Count[$i]; Value:0 ]
                                    End If
                                End Loop.

                                Then the second loop pulls out the email address of each person, and adds that to a $To variable.  Then I use the names and counts to create another additive-built text variable, then add that to the rest of the $Body variable.  Those are then passed into the send email routines.  Works pretty well, I think.


                                -- J