compare value in portal with value outside of portal

[greerso]

In short, I have a portal in a layout related by a calc field that is not the primary key.  I use conditional formatting in the portal for when the foreign key does match the primary key in the related table.  That works.  What does not work is a calc field in the portal table, placed on a portal row that is "foreign_key = primary key", it always shows a 0 even when the keys do match.  Stranger still is that in the data viewer, the calc does work and updates correctly for each portal row I select.

 

The longer version is that I'm working on a contacts solution where I have company records and individual records in the same table and addresses for each in a related table.  I want to show company addresses in the employee (related) records so I made a calc field that on an individuals record concatenates its primary key with a line break and the foreign key for the related company record.  This got my company addresses showing up in the individuals records, just need to identify those addresses which is why I tried to create a simple calc for contact::primary_key = address::foreign_key expecting a result of 1 for a match and 0 for no match.  I only get 0's.  Stranger still (to me), if I make a calc field in the address table that simply is the primary key of the contacts and place it in the portal row, the calc works.

 

Any help would be greatly appreciated.

 

Danny

 

 

[LVie]

Danny,

 

Have you checked to make sure the field type of both keys is the same, either both text fields or both number fields. If they don't match sometimes there are inconsistent results. 

 

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

[greerso]

Field type was my first thought, they are both of the type number.  They calc does give the expected result in the data viewer and works for conditional formatting in the portal.  It just not give the expected result when it is a calc field placed in the layout.

 

I have attached a very simple database with a sample of what I mean.  Create a new records and enter a number in the layout, then a matching or none matching number in the portal, for non matching you should see a 0 and matching a 1.  Instead, its all 0's unless you put the primary key from the parent form on the portal.

Untitled.fmp12.zip

[greerso]

I have now found that if I remove the primary key from the portal, the calculation will continue to work on that record only, new records still do not work.

[LaRetta]

They calc does give the expected result in the data viewer and works for conditional formatting in the portal.  It just not give the expected result when it is a calc field placed in the layout.

 

Hi Danny,

 

Perspective is pretty-much everything in FileMaker.  When the calculation is outside of the portal (but it is the portal table's field), the parent can only see the FIRST related child record according to the sort order of the relationship (or natural sort order of creation).

 

So if you place the calc above the portal, you will see that it always evaluates according to its first child portal record.  If you go change the sort order on the child (in that relationship) to descending sort on number, that calculation will instead see a different record as its first and will evaluate based upon it instead.

[LVie]

The sample you sent works correctly with or without the primary key displayed. However, this is not set up the same way that your problem above is. The sample tables are related by primary to foreign key so there is no discrepancy with that setup. There are a couple more things you can check in your problem file.

 

1. Is the portal from the context of the relationship you think it is?

2. Are you using an unstored calc in the relationship?

3. Are you using simple serial numbers for your primary keys? If they are the same numbering system in both parent and child that can give unexpected results when there are duplicates unrelated to the pk = fk relationship.

 

I would have to see the relationship structure and the actual calc to figure out how you are getting from one to the other and why it isn't working. Your other option which would probably be more solid would be to use an ExecuteSQL statement in your calculation to identify company addresses.

 

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

[greerso]

Hi Danny,

 

Perspective is pretty-much everything in FileMaker.  When the calculation is outside of the portal (but it is the portal table's field), the parent can only see the FIRST related child record according to the sort order of the relationship (or natural sort order of creation).

 

So if you place the calc above the portal, you will see that it always evaluates according to its first child portal record.  If you go change the sort order on the child (in that relationship) to descending sort on number, that calculation will instead see a different record as its first and will evaluate based upon it instead.

This is exactly my problem.  I was hoping that once I understood the problem, I would be able to think of a solution.  Not so.  I still dont understand why the data viewer does work and conditional formatting works.

 

Is this a problem that there is a popular workaround for?

 

Thanks for the explanation, its a relief to know what is happening.

The sample you sent works correctly with or without the primary key displayed. However, this is not set up the same way that your problem above is. The sample tables are related by primary to foreign key so there is no discrepancy with that setup. There are a couple more things you can check in your problem file.

 

1. Is the portal from the context of the relationship you think it is?

2. Are you using an unstored calc in the relationship?

3. Are you using simple serial numbers for your primary keys? If they are the same numbering system in both parent and child that can give unexpected results when there are duplicates unrelated to the pk = fk relationship.

 

I would have to see the relationship structure and the actual calc to figure out how you are getting from one to the other and why it isn't working. Your other option which would probably be more solid would be to use an ExecuteSQL statement in your calculation to identify company addresses.

 

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

The solution I provided only appears to work correctly if you use an existing record that I once had the primary key displayed on or (according to the post by LaRetta) if the first result is a match, but then all non matching results in the portal will be wrong.  If you create a new record , add a number, then in the first portal row a non matching number all subsequent rows will say they're non matching whether they do or not.

[LaRetta]

If you would name your example as your original post it will help.  As is, I cannot see what you want to accomplish, sorry.  Make the file look like your problem, referencing same names you used originally so we can see what you mean. 

 

Please forget everything you have tried to this point.  Please don't tell us what you have tried and what doesn't work but instead focus on modifying your demo, naming the tables and fields to represent REAL life and put in a few real logical records and then give a specific example of what data you want to display where and then we can nail it quickly.  Otherwise we can play a guessing game for quite some time. 

 

Hang in there, we'll get you there.

[greerso]

Thanks for the tips on proceeding.  I am actually working on a better example right now.  My actual solution is a work in progress mess.  I'm building a simplified version to show what is happening more clearly, with actual names, calcs and relationships.  Will post shortly.

[greerso]

In the example file I built, surprisingly everything works as expected.  I will spend some time comparing to learn where I was going wrong.  In case you're interested, I've attached the example.

contacts.fmp12.zip

[LVie]

That is often very instructive. You should be getting close to an answer.

 

L