11 Replies Latest reply on Sep 29, 2014 1:50 PM by IsaacKnoflicek

    Portal Filtering

    IsaacKnoflicek

      Title

      Portal Filtering

      Post

      I've got a modified version of the Invoices Starter Solution from FileMaker 13 and I'm having some issues filtering in Portals.

      I created a new layout and I want to have 3 portals each showing records from the "Invoice Data" table at different points in a workflow.  The layout is based on the "Invoice Data" table, and the portals are related to a duplicate of the "Invoice Data" table joined to the original on a primary key field I added (InvoiceDataID).

      The conditions I'm trying to use are if "Product ID Match field" and "Ordered" are empty show up in the first portal.  I've got dozens of records that meet those criteria, but I can't get any of them to show up.  I've even tried simplifying it to just "Product ID Match Field" is empty with no success.

      Is there something quirky about the way I'm doing this that would prevent it from filtering correctly?

      Thanks,
      Isaac

        • 1. Re: Portal Filtering
          IsaacKnoflicek

          Quick update, I tried turning off filtering on the portal and it's just displaying one record, so something has to be wrong with the way I'm linking the tables together?

          Thanks,
          Isaac

          • 2. Re: Portal Filtering
            philmodjunk

            Better check the value of Invoice ID Match field in both Invoices and Invoice Data tables.

            • 3. Re: Portal Filtering
              IsaacKnoflicek

              I'm not using the Invoices table here.  It's just Invoice Data and a duplicate of Invoice Data.  I confirmed that they're both joined on the same field, an auto numbering "Invoice Data ID" field I created which is populated and should work as far as I can tell.

              Thanks,

              Isaac

              • 4. Re: Portal Filtering
                philmodjunk

                Nevertheless, it's the value of the match fields used in your relationship that will control what records appear in the portal.

                Frankly, the relationship you describe, does not make sense to me as a way to get the results that you want as described in your original post.

                • 5. Re: Portal Filtering
                  IsaacKnoflicek

                  I'm probably doing something dumb, if there's a better way please let me know.

                  The records in "Invoice Data" are either waiting to be ordered, waiting to be received, or waiting to be completed.  I thought the portal functionality would be good for this use.  I couldn't have the portal relate to the same table as the layout itself, so I created a duplicate of the "Invoice Data" table and used that.

                  I know I can do this with one layout and scripts that do the filtering, but I thought the portals would be a lot cleaner.

                  Thanks for your help.

                  Isaac

                  • 6. Re: Portal Filtering
                    philmodjunk

                    But why use a table layout based on Invoice data and not Invoice?

                    Won't all the Invoice Data "line items" be for a single invoice?

                    Note that all Invoice Data records for the same invoice will have the same value in Invoice Data::Invoice ID match field.

                    If you want, you can set up several portals to Invoice data on an Invoices based layout. Then specify a different portal filter for each portal so that each set of line item entries for the same invoice, can appear in the appropriate portal based on the presence or absence of data in the Invoice Data fields.

                    • 7. Re: Portal Filtering
                      IsaacKnoflicek

                      Okay I figured out the problem, but I'm still not sure how to do this right.  The one record I was seeing in the portal with the filter off was the one active record in the layout.  So as I flipped through the active records on the toolbar a different one would pop up in the portals.

                      So now I just need to figure out how to basically have the portal show ALL Invoice Data records, so I can then widdle it down with the filter.  Any ideas?

                      Thanks,
                      Isaac

                      • 8. Re: Portal Filtering
                        philmodjunk

                        To start, please read my last post again.

                        You seem to be working with Invoice Data items without doing so in the "context" of a particular Invoice and that seems very strange.

                        But a relationship that uses the Cartesian join operator (x) can match any record in one table to all records in the other.

                        But relying solely on a portal filter to "whittle down" the number of records shown can result in layouts that are very slow to update once you get several 1000 records in your table...

                        • 9. Re: Portal Filtering
                          IsaacKnoflicek

                          The "Invoice" is really just a way for Labs to request multiple items all at once from us, once it's submitted we don't care about the grouping of "Invoice", we just want to know the actual items they requested.

                          We're just middlemen, we order these items from somewhere else, the items inside an Invoice might need to be ordered from different vendors, or might be needed at different times, or orders may be grouped or split up by whoever we're buying them from.

                          Does that make sense?  We don't want to be jumping through invoices, we just want a list of the resultant line items from the "Invoice Data" table so we can action on them as necessary.

                          I'll play around with the (x) relationship operator, but I still feel like I'm missing something and there's probably a better way to do all this.

                          Thanks,

                          Isaac

                           

                          • 10. Re: Portal Filtering
                            philmodjunk

                            I suspect that you really don't want to view anything close to "all records". There are many different ways to use match field values to reduce the list to a smaller subset of records. A relationship might be used to match to all records from "unfilled" invoices, for example. Or a date field could be used as a match field to exclude all related records older than that date...

                            Or you can simply not use portals.

                            A list or table view of Invoice Data could be used for this by using finds and sorts to limit your records to those you want to work with (Find) and to group them by the "status" they currently have (sort).

                            • 11. Re: Portal Filtering
                              IsaacKnoflicek

                              Thanks for the help, you're spot on.  I looked at how the Starter Solution handled the Company Dashboard and copied that.  So I created a new table with two fields and a single record.  Then I related those two fields to the Invoice Data table such that it would only show me the items that weren't "Complete".

                              I associated my layout with this new table, and the portals with "Invoice Data" and got the expected results.

                              Thanks again for your help!

                              Isaac