1 2 Previous Next 18 Replies Latest reply on Jun 16, 2017 11:01 AM by user1069

# Is it possible to create a relationship from a constant to a variable value?

I want to be able to create a portal that shows records with a balance greater than zero. A filtered portal takes too long to load when there are thousands of records, so I was wondering if there was a way to create a relationship that would accomplish the same task. In my solution, Table A would have a constant field (presumably in this scenario it would have to be some sort of calculation) that would match against a balance due field in Table B. Is this possible? Note that in some records there will be a value less than zero (a credit), for which I would want a separate relationship to display refunds owed to customers. Thanks.

• ###### 1. Re: Is it possible to create a relationship from a constant to a variable value?

Hi Mike

Yes what you describe is definitely possible, and you look like you're on the right track!

You can create a stored calculation, say c_zero defined to be equal to 0 in Table A and then have 2 relationships:

• c_zero > balance AND matching keys : shows all related records with a negative balance
• c_zero < balance AND matching keys : shows all related records with a positive, larger than zero balance

Hope this helps

Chloe

• ###### 2. Re: Is it possible to create a relationship from a constant to a variable value?

Def can do.

Only consideration is whether the fields in the relationship are indexed.

Indexing is a significant topic for FM and it would be good to do some study..

The field(s) on the "many" side must have an index for the records on the "many" side to be accessible from the "one" side

this has implications for portals and the go to related records script step.

FileMaker Pro Indexes and Related Problems

FileMaker Indexing – There can be too much of a good thing

1 of 1 people found this helpful
• ###### 3. Re: Is it possible to create a relationship from a constant to a variable value?

Unfortunately that didn't work. The balance in table B is a calculation and with this relationship it isn't filtering. I tried it this way:

Table A                                                  Table B

_Balance (global field calc of 1)    =   _Balance: If ( GetAsNumber(TOTAL STMT CURRENT BALANCE) >0;1;0 )

_Constant (global field calc of 1)   =    _Constant (global calc of 1)

I tried it as a Cartesian ( x ) as well but that didn't work either. What I"m getting is all the records showing in the portal and not filtered based on the balance.

• ###### 4. Re: Is it possible to create a relationship from a constant to a variable value?

I don't think I can make it indexed because the value in table B could change when a payment is made. So it's the match field is set to unstored.

• ###### 5. Re: Is it possible to create a relationship from a constant to a variable value?

post the file

1 of 1 people found this helpful
• ###### 6. Re: Is it possible to create a relationship from a constant to a variable value?

this is one of the reasons why most FM inventory/puchasing/accounting systems use scripted processes to set "balance" type fields. It avoids trying to leverage unstored calculations in relationships

2 of 2 people found this helpful
• ###### 7. Re: Is it possible to create a relationship from a constant to a variable value?

Is there another way to approach this? I didn't create this solution so I'm stuck with a boatload of calculated fields. I was trying to build a dashboard of sorts to allow users to quickly see balances due and refunds owed. The filtered portal I was using seems to load just as fast as any of the relationship solutions mentioned here. So I think I'll go back to that. When we finally get around to rewriting our financial section, I can revisit this.

• ###### 8. Re: Is it possible to create a relationship from a constant to a variable value?

The approach would be to silently copy the balance in a numeric field every time the Invoice (or whatever that is) gets updated. You can index that one and the relationships would work.

• ###### 9. Re: Is it possible to create a relationship from a constant to a variable value?

I think I've come up with an easier solution. I created a number field in Table B (the one that has the balances) and added a script step to replace its contents with the current statement balance before going to the Quick Look layout. The relationship between Table A and Table B no longer involves a calculated field. The replace function is very fast and the relationship will update each time the user goes to the Quick Look layout.

• ###### 10. Re: Is it possible to create a relationship from a constant to a variable value?

Ugh it's still not working. Now no records are showing.

• ###### 11. Re: Is it possible to create a relationship from a constant to a variable value?

Did you specify that the field is numeric instead of  text ?

• ###### 12. Re: Is it possible to create a relationship from a constant to a variable value?

Yes. I think the trouble I"m having is how to make the relationship join to three things, the case id, the case status and the balance. (For the time being I haven't been worrying about the case status yet since I can't get a basic relationship to work.) There are other relationships set up where a simple _Constant field in Table A links to a _Constant in Table B, so I think the trouble is that if _Constant = _Constant (which will be a value of 1) and _BalanceDue (TableB) > _Balance (a global calc in table A set to 0), it's just not working. I think in order to link the records the record ID somehow has to be linked. But Table A doesn't contain data. It's just a one record table for the purposes of displaying portals on a layout. Sorry, I'm new to this idea of a dummy table. Another programmer set a lot of this up so I'm trying to copy his examples. He also just mostly used Cartesian relationships between the tables and I'm actually trying to compare a value.

All of this works in a filtered portal situation where the relationship between Table A and B is Cartesian from _Constant to _Constant and the filtering is done on the layout. But with thousands of files it takes about a minute for the results to display.

• ###### 13. Re: Is it possible to create a relationship from a constant to a variable value?

It HAS to work. As coherentkris  said, post the database or a clone with a few records in it.

• ###### 14. Re: Is it possible to create a relationship from a constant to a variable value?

It doesn't have to be Cartesian to use the filtered portal approach. You can set up a relationship that matches by Case ID and status, then filter on the balance only. This will greatly reduce the number of times that you have to evaluate the filter expression.

1 2 Previous Next