14 Replies Latest reply on Apr 7, 2017 7:04 AM by beverly

    Sql Distinct Value

    GJBDJC

      I would like to set up a portal which shows only unique values from a related table.

       

      In my Quality Details table, I store the test results for each sample taken.  A Finished Product Lot may have one or many samples and a given project may have one or many lots.

       

      I would like to filter a portal to only show the unique lots of a related project.  I tried the following:

       

      ExecuteSQL (

             "SELECT DISTINCT \"FP Lot Number\"

             FROM \"Quality Details\"";

             ""; ""

      )

       

      FP Lot Number is the field containing lot numbers and the field is a text field (Lot numbers may contain Alpha values: BDA1234)

      Quality Details is the table containing FP Lot Numbers.  Anyway, the filter shows nothing, zippo, nada?????

        • 1. Re: Sql Distinct Value
          beverly

          You will not get related information from the query. ExecuteSQL() does not know that these are to be related unless you tell it.

           

          You must make the JOIN in the FROM clause and the match (as you have in your relationship dialog).

           

          I don't know what other fields you have so cannot get more specific on the query.

           

          If you have a "looked-up" named: FP Lot Number, in the table: Quality Details, then this query works. If OTOH, the field is already related where does it exist and what are the fields for the match to make it show as a related field on the layout showing the samples.

          beverly

          • 2. Re: Sql Distinct Value
            fmpdude

            Don't you mean something like this below.

             

            (I'm assuming you have a single table. However, if you have multiple tables, you need to do a join as beverly said.)

             

            If this isn't what you meant above, please post some representative data. Your posting is not clear without representative data.

             

            • 3. Re: Sql Distinct Value
              philmodjunk

              And how would you use that query as a "portal filter"? A portal'a filter expression has to produce a true or false result and be evaluated against every record in your set of related records.

               

              You could use this calculation  in place of a portal, but not as a filter expression nor as a match field in a relationship . It would match to records in a relationship but would not give you a set of unique values when it did so.

              • 4. Re: Sql Distinct Value
                user19752

                You need to decide which record should be shown in portal, for example "oldest" can be detected using Get(RecordId) calculation field

                 

                SELECT MIN(recid)

                FROM \"Quality Details\"

                WHERE project=?

                GROUP BY \"FP Lot Number\"

                 

                This get record id list distinct on lot number, then you can make portal using this as related field.

                main::this = inPortal::recid

                • 5. Re: Sql Distinct Value
                  beverly

                  I would never rely on Get ( RecordID ) for anything other than temporary this-works-now-if-the-data-is-not-exported-and-reimported. It is a very useful feature for say, web publishing and you get results returned and then can be specific to edit or delete or update a particular record. It is unique for that transaction. "Creation Order" is not one of those things for which I'd use RecordID. Something else should be used.

                  beverly

                  • 6. Re: Sql Distinct Value
                    kazznfx

                    Hey There,

                     

                    Without knowing your structure it's a little hard to answer, but I have used SQL to filter portals before. But I used a multi-key field (complex key field).

                     

                    I use the SQL query to fill the complex key field, and that validates the relationship to the records I'm specifically interested in. It's also very light and quick.

                     

                    So if you use SQL query to fill in a multi key field, this should work for you. There are lots of resources about complex keys available online. It's basically just a key field that, rather than having a single value, has multiple separated by carriage returns. If any of the values in the list match related records, the relationship is validated and their data shows.

                    • 7. Re: Sql Distinct Value
                      kazznfx

                      OH! one other thing I should mention. For your SQL query, I think all you'll need to make that work is a WHERE clause. It looks like you're just looking for data related to the current record.

                       

                      So you would essentially add a WHERE clause, feed it a "?" parameter, and define that parameter to be your primary key.

                       

                      Lots of info about the WHERE clause online and in the SQL manual from FileMaker

                      • 8. Re: Sql Distinct Value
                        beverly

                        Good suggestion, but DISTINCT (on a field/column) already narrows down a list). WHERE can help only if there is further narrowing needed.

                        SELECT DISTINCT city

                        FROM addresses

                        WHERE state IN ( 'CA', 'WA', 'OR' )

                        // only the West Coast, for example

                        This reply, above) is an other example of using the WHERE:

                        • 9. Re: Sql Distinct Value
                          fmpdude

                          And "HAVING" will narrow down a GROUP BY.

                          • 10. Re: Sql Distinct Value
                            beverly

                            I do recall seeing that in the "The Missing FM 12 ExecuteSQL Reference". LOL

                            • 11. Re: Sql Distinct Value
                              user19752

                              I quoted as "oldest" to mean it could be really oldest, but I was bad for writing. OP looks not mind about which record in group (having same value on FP Lot Number field) is shown in portal (only a field may be there), so "oldest" may not nead really oldest.

                               

                              I believe this is the real case for "this-works-now-if-the-data-is-not-exported-and-reimported", but I forgot to write the SQL calculation field (main::this) should be unstored. This is calculation field so will never be imported.

                               

                              My point were, inPortal::recid field should be unique in each group (FP Lot Number), so if there is primary key field in the table, it could be used, not need to be bothered by Get(RecordID).

                              For the purpose it is not need to be unique in whole table, so if there is no such field, Get(RecordID) may be best. Making it unique in group become more complex, Get(UUID) is too long.

                               

                              • 12. Re: Sql Distinct Value
                                user19752

                                Where may be need for project, since OP wrote "the unique lots of a related project", it is already used for portal relation.

                                • 13. Re: Sql Distinct Value
                                  Menno

                                  I don't want to steer you away from using SQL and maybe I'm looking at your question the wrong way, but you wish to create a portal to show the unique "fp lot numbers" so why don't you create an additional table with just the lot numbers? That way you do not need to filter any portal.

                                   

                                  There are sveral scenarios that you can follow to achieve this, but this is an example: Every time you create a quality-record and a lot number is entered, you could check for an existing lot number against that additional table. If the lot number doesn't exist in there, create one. Via a multilevel relation, the unique lot numbers can now be shown in a portal to the additional table.

                                  • 14. Re: Sql Distinct Value
                                    beverly

                                    Primary keys and Foreign keys, (unique identifiers making the relationship), but never the Get ( RecordID ). This should never be used for relationships. And I wouldn't rely on them for filtering (which is just another 'relationship'), especially if unstored.

                                     

                                    beverly