8 Replies Latest reply on Jul 31, 2016 1:22 PM by richardsrussell

    How To Isolate Only Positive Related Values

    richardsrussell

      Here's the table structure I'm using for a client who runs a mail-order retail operation:

       

      Thalia Structure.jpeg

       

      Each invoice is connected to a "DHLinx" record that identifies both (a) the Destination (the name and physical address of the company or household to which the product should be sent) and (b) the Human (the name and eddress of the person who placed the order). Over time, a company's order-placing person will change, which is why I connected the invoices to the "DHLinx" (Destination-Human links) table rather than directly to the "Destinations" (location) table.

       

      But my client wanted to see, for any given Destination or Human, when the most recent order had been placed, and for what amount. Thus I created the 2 table occurrences (TOs) of "DHLinx" identified as "Latest Sale" (via either H for Humans or D for Destinations). Those relationships are defined to be sorted by reverse chronological order, so the most recent invoice date would be the top record listed in a portal. Or, more pertinently, the only record visible if I just displayed, without a portal, a field from "DHL Latest Sale vD" on a "Destinations" layout or from "DHL Latest Sale vH" on a "Humans" layout.

       

      And it appeared that all was working well until we got to the issue of negative numbers. Let's say that one of my client's customers placed an order for $1000 that was invoiced on September 1. That date and amount would be stored in the "Invoices" table and displayed (via "DHLinx") in both "Destinations" and "Humans". Great! Exactly what was required and provided.

       

      But then the client's customer became entitled to a refund, for let us say $150, which was generated on December 20 and recorded as a negative transaction in the "Invoices" table. Now the "Latest Sale" information on display in both "Humans" and "Destinations" shows –$150 on Dec. 20 instead of $1000 on Sep. 1. My client doesn't want that. He wants to see the last positive invoice (that is, one reflecting an actual sale).

       

      The question is how to get at it. The only way the "Invoices" table knows whether a given record is for a positive or negative amount is to add up all the "LineItems" records attached to it. It can only do that via the relationship to the "LineItems" table, which means the whole positive-or-negative question turns on the value of a related field, which can't be indexed and thus can't be used as the basis for a relationship of its own, similar to the "Latest Sale" connection described above. And I can't filter for positive values via the relationship definition, only via a portal definition, except I'm not using a portal.

       

      Any suggestions?

        • 1. Re: How To Isolate Only Positive Related Values
          DavidJondreau

          OK, I've read this and I'm not sure I understand it. What does the DHLinx table represent? Is each record a person/destination combo? Or also an invoice? Also, it's unclear why the invoice total being unstored is an issue. You're not basing a relationship on that field, right?

           

          Why not use a portal? You can make it a single line and make the borders invisible. If you have a large child record set, the performance could be troublesome though.

           

          Other options:

          Filter for positive via the relationship. Add a calc field = 0 and add a ">" operator criteria.

          store the Human ID and the Destination ID in the Invoices table, then have your display should from an Invoices TO ( instead of the DHL TO ).

          Store the Invoice total and update it using scripts (if unstored is an issue).

          1 of 1 people found this helpful
          • 2. Re: How To Isolate Only Positive Related Values
            paulapalooza

            I'm no expert, but what I'd do if I were in your shoes is go to the Inspector and "hide when" Latest Sale <$0.01.

            • 3. Re: How To Isolate Only Positive Related Values
              planteg

              Hi Richard,

               

              if you can change the tables definition, here is what you could to:

               

              • add a calculated fields on table Invoices where you store the Invoice total. The calculation could be done by ExecSQL()
              • then using ExecSQL again, you could find the last Invoice with a positive amount with something like

              "

              SELECT Invoices.Subtotal, Invoices.InSeq1, DHLinks.DesSeq FROM Invoices
              LEFT JOIN ON Invoices.DHLSeq = DHLinks.DHLSeq1

              WHERE (Invoices.Subtotal > 0.00 AND DHLSeq1 = ?

              ORDER DESC Invoices.Subtotal

              "

              ...

               

              You could do the same with a Human instead of a Destination.

              • 4. Re: How To Isolate Only Positive Related Values
                richardsrussell

                What does the DHLinx table represent? Is each record a person/destination combo? Or also an invoice? Also, it's unclear why the invoice total being unstored is an issue. You're not basing a relationship on that field, right?

                Each "DHLinx" record is "a person/destination combo". Each such record is at the "one" end of a one-to-many relationship with "Invoices".

                 

                To explain why indexing within the "Invoices" table is an issue, let me use an example from the perspective of the "DHLinx" table, where my primary key is DHLSeq1. Let's say that DHLSeq1 Record #1234 is for the XYZ Co. and its purchasing agent Pat Smith. Over the years, Pat has placed 25 orders with us and gotten 2 refunds. Thus there are 27 records in "Invoices" that show a value for DHLSeq2 (a secondary or foreign key) as 1234. Those are the 27 records that show up in the "Invoices" portal on my main "DHLinx" layout for XYZ Co./Pat Smith.

                 

                What I tried to do (but failed at) was to create a different field in "Invoices" that could be used as the object of a different relationship. I called this field "DHLSeq Sale". It was a Calculation field with Number result, and this was the formula:

                     DHLSeq Sale = If ( LineItemTotal > 0; DHLSeq2, "" )

                If I'd been able to establish a new relationship from DHLinx::DHLSeq1 to Invoices::DHLSeq Sale, it would've yielded only the 25 "Invoices" records that represented actual sales, which is what I was after.

                 

                The rock it foundered on was, of course, the fact that "LineItemTotal" isn't stored directly in the "Invoices" table. It's calculated based on related records from the "LineItems" table, and that fact makes it unstorable in "Invoices". Unstorable means it's unindexable. Unindexable means it can't be used to create a relationship.

                • 5. Re: How To Isolate Only Positive Related Values
                  richardsrussell

                  Thanks for the suggestion, Paula, but I'm not trying to hide the most recent transaction if it's a negative value, I'm trying to show an earlier transaction in its place, the next earlier one that has a positive value. I need to find some technique for moving the negative value out of the way, so the positive value will show thru.

                  • 6. Re: How To Isolate Only Positive Related Values
                    DavidJondreau

                    Or you could do that in the calculation dialog of a button bar on the layout itself! No need for a field.

                     

                    Beware the uncommitted record issue though with ExSQL. May be better to have it on a popover that commits all open records on click.

                    • 7. Re: How To Isolate Only Positive Related Values
                      ron.harris

                      How about add a field to store transaction type (R for refund; S for Sale) and only display records that = S

                      • 8. Re: How To Isolate Only Positive Related Values
                        richardsrussell

                        I ended up using a variant of Ron Harris's suggestion. It entailed creating a "No Sale" field within the "Invoices" table that gets a "1" (displayed as a checked box) auto-entered if the first related "LineItems" record to be created has a negative value. This is sub-optimal for several reasons:

                             • If subsequent "LineItems" records have positive values, so that they net out to a total positive value for the "Invoices" record as a whole, that doesn't cancel out the auto-entered "No Sale" check.

                             • I've had to leave the "No Sale" field enterable by the user, in case they need to adjust it manually, but that opens the possibility of their doing so inadvertently, either turning on a false positive or turning off a false negative.

                             • I now have 2 different ways of tracking what should be only a single variable — whether the "Invoices" is overall positive or negative — and that's not only more complicated but also a violation of the basic rule of databasing that any given datum should be stored only once.

                             • I've had to create some (unstored, bright red, big type) alarm messages that warn the user when these 2 methods of tracking overall negativity are out of sync with each other. Ugly! Looks like a kludge because it is a kludge.

                         

                        Still, this technique is mainly getting the job done — I can use the "No Sale" box as a filter to determine whether any given "Invoices" record should be passed thru to the "Latest Sale" calculation downstream in the "DHLinx" table — but it's not as nifty as if I'd been able to apply that filter directly to values coming into "Invoices" from "LineItems". I think that the inability to do so is a shortcoming in FileMaker Pro, and I've proposed a new idea here to implement it. I'd be gratified if y'all would go and upvote it.

                         

                        Thanks for your time and attention.