How to filter data in a table with a table occurrence..??

[angelleye]

I have a table named transactions with a field named ipn_status.  The status can be either "Verified" or "Invalid".  I'd like to make separate table occurrences that will display only Verified or Invalid records.

In the past I would do this by creating a global field in the table called something like relVerified, and I would just put the value "Verified" inside this field.  Then I could create a new table occurrence of the same transactions table, and I could setup the relationship like transactions::ipn_status = transactions::relVerified.  This would get me what I want.

Not long ago, though, somebody pointed out to me that this is no longer necessary, and there is a better way to handle such a scenario.  I can't for the life of me remember how to do it, though..??

Any information on this would be greatly appreciated.  Thanks!

[webko]

Filtered Portal

[angelleye]

17 minutes ago, webko said:

Filtered Portal

Hmmm...I don't think that was it.  I'm not looking to display related records within a portal on a layout.  I'm looking to create an entirely separate layout that would display nothing but the filtered records.  So I would end up with 3 table occurrences from the same transactions table:  transactions, transactions_verified, and transactions_invalid.

So then I could create a layout based on transactions_verified and it would only show the verified records.

Again, I can get it done using a global field with the value I want to filter by, but there was a much more elegant way to get it done added in FM not many versions ago that somebody pointed out to me...which I can't remember now.

[comment]

2 hours ago, angelleye said:

So then I could create a layout based on transactions_verified and it would only show the verified records.

That's not how it works. A table occurrence does not filter records by itself. You would have to use a Go to Related Record script step to perform the filtering for you. And you would not need dedicated layouts for this - you could re-use a single layout to show the resulting found set.

As an alternative, you could use a script triggered OnLayoutEnter to perform a find. Here, you would need three layouts - but not the additional table occurrences. Note that the extra layouts are only needed if you navigate layouts manually. Otherwise you could again re-use the same layout and have the navigating buttons call a script that finds the required records and shows them in the same, one and only, layout.

 

Edited January 15, 2016 by comment

[angelleye]

3 minutes ago, comment said:

That's not how it works. A table occurrence does not filter records by itself. You would have to use a Go to Related Record script step to perform the filtering for you. And you would not need dedicatet layouts for this - you could use a single layout to show the resulting found set

As an alternative, you could use a script triggered OnLayoutEnter to perform a find. Here, you would need three layouts - but not the additional table occurrences.
Note that this is only needed if you navigate the layouts manually. Otherwise you could again re-use the same layout and have the navigating button call a script that finds the required records and shows them in the same, one and only layout.

Ah, yes, sorry.  Just wasn't thinking straight there.  I guess I'll probably just do it the way I always have.  I just wish I could remember where it was somebody told me the better way.

It was in a forum somewhere (here, experts exchange, stack overflow, etc.) and somebody else was asking about to setup a relationship the way they needed.  I answered with the method I'm talking about here, where you create a "relationship field" that always has the value you're matching on.  Somebody else then responded telling me that was no longer necessary because FM introduced a new feature, and it worked great, but now I can't find it.  Doh!

[comment]

Well, there aren't that many options, because if you are looking to establish a found set, there are exactly two methods to do it: either perform a find or do Go to Related Record (I believe that for the current purposes we can disregard the third method of omitting individual records).

[JohFMhack]

And what if angelleye wants value list based only on the Transactions which are Verified?  Kind of using this as an example. Value lists can not have calculations. So therefore I would like my value list to be populated by a table occurrence field. Any suggestions?

[comment]

12 hours ago, JohFMhack said:

And what if angelleye wants value list based only on the Transactions which are Verified?

Then again they have two options:

1. Define a calculation field =

If ( Status = "Verified" ; TransactionID )

and define the value list to use values from this field;

2. Use a relationship to filter only verified transactions, and define the value list to include only related values from this relationship. The exact details depend on where they need this value list (and in most cases there will be more than one way to set this up).

 

 

Edited July 9, 2016 by comment

[JohFMhack]

Thanks Comment!  That worked great with the calculation field.