Open Balance

["... you mean these fans?"]

Hi everybody,

My client wanted to see the open balance from their "Invoice" table on their "Contacts" table so I built the following relationship ... 

Open_Balance = Sum ( pk_Contact_ID to fk_Contact_ID::Invoice Total ) - Sum ( pk_Contact_ID to fk_Contact_ID::Invoice_Paid ).

Now my clients wants to see this open balance in a portal on another layout on the "Home" table, this is a "One" record table. 

Since this one record table has no Contact_ID I attempted to build the following "Relationship" g_Zero ( HomeTable ) < Open_Balance ( Contacts Table ) ... When I try to build this relationship Filemaker "Grays" out the "Allow Creation of Records ..." unless I use an "Equal" sign which is not what I need ... I need the "Greater Than" ... and since I am not on an "Actual Contact Record" I'm not allowed to see a list of contacts with an "Open Balance" and the only why I know is to use a "Global" fields to my contacts table, then the relationship fails due to the "Relationship calculations within the "Open Balance" calculation.

So I tried to build a "Field" that produces a "One" but this too fails do to the "Open Balance" being a related calculation ... come on man ... annoying.

Anyway, anybody out there who maybe able to provide some assistance for this relationship I would be grateful.

Thank you.

Tom

Edited January 20, 2016 by Snozzles

[comment]

1 hour ago, Snozzles said:

so I built the following relationship ... 

Open_Balance = Sum ( pk_Contact_ID to fk_Contact_ID::Invoice Total ) - Sum ( pk_Contact_ID to fk_Contact_ID::Invoice_Paid ).

This is not a relationship. I think I understand what you're trying to say, but really ... that's a very confusing way to describe your setup. (And, IMHO, a very awkward naming scheme).

 

1 hour ago, Snozzles said:

Now my clients wants to see this open balance in a portal on another layout on the "Home" table, this is a "One" record table. 

Again, you don't say and we are left to guess they want the portal to show only clients with negative balance. Since the balance (in the Contacts table) is an unstored calculation, you cannot use it as a matchfield on the child side of the relationship. If the number of Contacts is not too large, you could make all contacts related and filter the portal.

Note that this will get progressively slower as the number of contacts - and their invoices - increases.

 

1 hour ago, Snozzles said:

When I try to build this relationship Filemaker "Grays" out the "Allow Creation of Records ..." unless I use an "Equal" sign which is not what I need

They should not be allowed to create or edit related records from a one-record table. This will not work well when there are multiple users.

 

Edited January 20, 2016 by comment

["... you mean these fans?"]

Thank you Comment,

I was just forwarding information so you guys can understand my issue and maybe provide a solution ... I am fully aware of why what I am asking for is not available.

I guess I should of just ask for a way to "FORCE" a un-indexable field to be indexable.

I must have access to this un-indexable "Balance Due" field ... I can't even perform a find on this field.

I mean what is the point have a relational database if you can't even use the information.

Anyway, any suggestions I would be grateful.

[comment]

52 minutes ago, Snozzles said:

I guess I should of just ask for a way to "FORCE" a un-indexable field to be indexable.

No, of course not. There is a workaround that will allow you to construct a relationship (or rather a chain of relationships) that depends on an unstored calculation field (search for "the Ugo method") - but as we now have portal filtering, I am not sure it's necessary to take it.

To have a truly indexed field, you would have to rely on a scripted update of the balance every time one of the contributing fields is modified in any way. While this is possible (and with large-scale solutions inevitable), it is also difficult to implement reliably.

 

1 hour ago, Snozzles said:

I must have access to this un-indexable "Balance Due" field ... I can't even perform a find on this field.

Why not? Unstored calculations can be searched.