Replace troi numbers plugin

[janslort]

I must replace the Troi Numbers plug in function. I was using it in FM6 to calculate totals of invoices for each customer in a portal (BalanceCalc)showing a running total of the related customer's invoices in each customer's account. The total field (TotalBalanceCalc)was placed on the related record (not in a portal). I could find > or < any value of the total field & sort the found set balances in assending or decending order. In FM8 the (BalanceCalc) function still works, but the (TotalBalanceCalc) field just shows up blank.

I replaced the (TotalBalanceCalc) field with a summary field of related records. That's O.K., but I can't do a "Find" balance > .00, or < .00, and when I have a "found" sub-set of the records, I can't sort them on the Total field. I tried making a Calculation field from the Summary field, because I remembered that can't be sorted. Still no "Find" or "Sort" works.

Any ideas?

Jan

[John Mark Osborne]

You can't search on a Summary field because it is based on the found set. Try using the Sum function in your customer table with a formula like this:

Sum(Account::Amount)

This calculation will be unstored so searches will be slower but at least you will be able to search and sort.

[janslort]

John Mark Osborne:

I tried it (several ways) and it still eludes me. I have two tables, "Invoices" and "Customers". I selected a field in the "invoices" table named "unpaid_Invoice_Balance" this is a calculation field ("Invoice amount" - "payments"). I created the new calc field "Sum of Invoices" with the calculation Sum(Unpaid_Invoice_Balance).

In the customers table I put it in a portal - show related records from "Invoices": Sum(Invoices::Unpaid_Invoice_Balance).

The result is showing only the unpaid invoice amount for each invoice, not the sum of all the related invoices for the customer.

What am I missing?

Jan

[Søren Dyhr]

That the relation graph isn't an ER!

Let's suppose you create your invoices from the client record (...lately have the "chair game" gone slightly out of fashion, need I say "Enron"), this means that you make a related record to a client so each invoice recieves a foreign key, before you enter something in the invoice's itemlines.

You do by it now change perspective or point of view, how would you now be able to watch what other invoices contain or add up to? If you have studied the whitepaper 'bout migration foundation would you know that you should add more table occurences to do so. Where would they fit?

What if we make a mirrored set on the other side of Client, would that do? Yes the post fm6 way of approaching data more than a table away makes this happen as well as the bidirectionality - so seen from the invoice's point of view is a new chain in the opposite direction of the relational structure where one client have many invoices.

In the query direction that begins with the invoice is the first TO on the path the Clients record (we're linking on the Client ID) next step is a new TO for invoice let's call it invoice2 this will give us all the invoices issued to that customer.

We could choose to stop here - but to aggregate on unstored values such as the invoice total, is not going to be particular efficient, because it have to wake up unstored fields in invicible invoices - so the proper way is to jump a bit further to gather all the itemlines linesum values which ought to be stored!

If this doesn't make sence take a look at the attached template or even better allow your self the oppertunity to read the whitepaper more thoroughly than first time :

I think that Troi can be excused for discontinuing their plugin!

--sd

Invoicing.zip

[janslort]

Wow, Soren. I thank you for all the effort you put in to try to explain this to me, but don't give up on me just yet. . . . I think (just reading what you said and without reading the 138 page whitepaper again)I just needed another TO, but I don't have line itms to worry about grouping, (thank heaven) only the single invoice amount identified by a unique invoice number keyed to a customer name. With a single TO I should be able to see all the invoices for a customer, (one to many) and so I should be able to aggregate them, as John Mark Osborne suggested, but so far I haven't had it come up right.

Jan

[Søren Dyhr]

TO, but I don't have line itms to worry about grouping, (thank heaven)

I can't say it surprises me much, but as soon as your invoice have more than one item listed is it a violation of 1NF ...the arguments for considering the normalforms are to keep each data in just one place and keep syncronizations at an absolute minimum.

If you persist in having your itemlines as a 1 NF violation, how would you then keep track with the number of items remaining in the warehouse you would have to think up a scheme to syncronize the two levels - What a propper datastructure actually seeks to convey, is - information the same way a wedding ring shows matrimonial status.

Your previous use of a plugin to take care of the measuring the total of the sale to the customer at hand. This is a syncronization because you write redundant data, which needs to get freshened once and a while if something gets altered or deleted from the invoice.

Why not recognize that such things could be an equation instead of an action performed in a timely manner? In filemaker could you with a decent structure make each alteration in a layout take care of the resolving of this equation.

--sd

[janslort]

Dear Soren:

Thanks a lot for your suggestion, I really appreciate it.

Jan