8 Replies Latest reply on Oct 21, 2016 2:34 PM by mw777rcc

    Displaying unmatched records in same layout


      I'm pretty new at FileMaker. I understand the concept, but I have a question that's bugging me.


      I'm working on a database to track shipments, when and how much is received, and to show the amounts still in transit.


      The setup is the Shipping table, with the Received table related by a concatenation of the SKU and Invoice number, which creates the unique match. I have no issue with getting the amount shipped and received to populate if the correct item is received. However, if we invoice or ship the wrong item, there's no match...  Is there a way to show these unmatched records?


      Just for an example, we ship SKU 8001, on invoice 1001, and the match record on the Shipping table is now 80011001. However, they received SKU 8002 on invoice 1001, and match record on the Received table is 80021001... I'd like to be able to see that we invoiced them for the wrong item, and that they received the right item. Right now, it just looks like the invoice is still outstanding. Is this possible in the same layout?

        • 1. Re: Displaying unmatched records in same layout

          First, there's no need to concatenate if the only reason is to match records. You can modify a relationship to match by more than one pair of fields so you can match records by Invoice and SKU fields in the same relationship.


          Extending that idea, you can set up a relationship using the = operator to match records by invoice, but using the ≠ operator with the SKU fields to match to all records with the same invoice but not the specified SKU.


          I rather suspect that this won't work for you however if you list more than one SKU on a given invoice as it would match to other SKUs that were ordered and shipped correctly that just don't happen to be the SKU specified.


          So please describe your set up of tables and relationships in more detail so that we can see how you are currently using both SKU and Invoice to match to a single record in the Received table. If we can pull up a list of SKUs for the current invoice, we can use that with the  ≠ operator to get what you need here.

          1 of 1 people found this helpful
          • 2. Re: Displaying unmatched records in same layout

            I am assuming that you have more than one SKU per invoice (if not then it is simpler still) but if so then the invoice could have a calculated field that lists all SKU/invoice numbers in a list field (summary type of list)

            You will need InvoiceID on both sides of the relationship even if you need to create this field on the other side by splitting off the invoice number from the joined field.

            then your relationship would be:


            InvoiceID = InvoiceID

            ListofCombinedIDs <> combinedID


            This will give you anything that was shipped on that invoice that shouldn't have been shipped.  Of course if they shipped too many of the same one and not the other then you would have to see the results in a portal to see what was going on.

            1 of 1 people found this helpful
            • 3. Re: Displaying unmatched records in same layout

              Phil - Yes, there are multiple SKU's per invoice. My relationships are setup as follows;


              Shipped::SKU_Invoice = Received::SKU_Invoice (The layout shows records from the Shipped table)


              Shipped::Invoice = Shipped2::Invoice (This is used to display a portal on the Shipped layout with all items and quantities shipped)


              The fields used from the invoice are; ShippingLocation, ReceivingLocation, Invoice, SKU, and QuantityShipped

              The fields created by the received location Excel sheet are; ReceivingLocation, ShippingLocation, Invoice, SKU, and QuantityReceived

              • 4. Re: Displaying unmatched records in same layout

                Yes, my method should work for you.  To set up a relationship to show a portal of all items that were received on that invoice but were not shipped on that invoice:


                Invoice = Invoice

                SKU_Invoice_List <> SKU_Invoice


                Where SKU_Invoice_List is a summary field (using list) of all SKU_Invoice fields for the current Invoice.

                1 of 1 people found this helpful
                • 5. Re: Displaying unmatched records in same layout

                  Sorry if this sounds odd... But how do I use the <> operator? Or am I missing something else? When I attempt to make a relationship between the two tables, the SKU_Invoice_List is greyed out, I'm assuming because it's a summary field?

                  • 6. Re: Displaying unmatched records in same layout

                    Sorry, I don't have the correct key on my keyboard.  In calculations in FileMaker both are equal to each other.  What you are looking for is the "Not Equals" join.  That is the equals with the line through it.  <> is just another way of typing it and it also happens to work, as I said, in calculations.

                    1 of 1 people found this helpful
                    • 7. Re: Displaying unmatched records in same layout

                      Ohhh... That makes sense. My only issue right now is that I can't join a summary field. Just to clarify, I've done the following steps in FileMaker Pro 14;


                      Created a summary field in the Shipping table, I chose "List of", then the existing SKU_Invoice field. The drop-down Summary Repetitions, I left as all together. When I go to create a relationship/join using this new summary field "SKU_Invoice_List", it's greyed out.


                      Thank you for your patience!

                      • 8. Re: Displaying unmatched records in same layout

                        Sorry, I missed a step.  You have to create a calculation field that points to that summary field.  Use the GetSummary function and both parameters of the function are the summary field you created.  You can now use that calculation in your join. (Should be unstored and so should be on the left side of the relationship (parent side))

                        1 of 1 people found this helpful