AnsweredAssumed Answered

How To Isolate Only Positive Related Values

Question asked by richardsrussell on Jul 20, 2016
Latest reply on Jul 31, 2016 by 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?