Filtering records in a portal

[Louisa]

I would like to be able to filter out records in a portal so that only records which fit a certain criteria appear.

On a layout I need to have two portals displaying information from the same database. One portal needs to display records with a creation date prior to a given date (this is not fixed like today's date or something like this but is determined by the user) and those which were created on that date. I'm not to sure how to do this.

I've figured out how to display the ones which were created on that date by creating a global field which combines the master records ID with the date and relating the portal on this. Can I do something similar for the other portal?

Thanks

[Kurt Knippel]

quote:

---

Originally posted by Louisa:

I would like to be able to filter out records in a portal so that only records which fit a certain criteria appear.

On a layout I need to have two portals displaying information from the same database. One portal needs to display records with a creation date prior to a given date (this is not fixed like today's date or something like this but is determined by the user) and those which were created on that date. I'm not to sure how to do this.

---

Filtering by date is really problematic, since you have infinite variation in possible data. If it is not the specified date and not earlier there is still almost infinite variants of the possible dates that match.

I would recommend the following. The user specifys the desired date, then click a "Filter" button. Do a find in the related file for the date and all prior dates (i.e. DateField <= SpecifiedDate). Then do a replace on a flag field (FlagField = 1) and have your portal display records based on that flag.

This is kind of a hassle, but a good way to approach this problem.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[slemson]

quote:

---

Originally posted by captkurt:

Filtering by date is really problematic, since you have infinite variation in possible data. If it is not the specified date and not earlier there is still almost infinite variants of the possible dates that match.

I would recommend the following. The user specifys the desired date, then click a "Filter" button. Do a find in the related file for the date and all prior dates (i.e. DateField <= SpecifiedDate). Then do a replace on a flag field (FlagField = 1) and have your portal display records based on that flag.

This is kind of a hassle, but a good way to approach this problem.

---

This solution will not work in a networked , shared solution will it? Won't everyone's portals change when one person changes the relationship criteria?

[Kurt Knippel]

quote:

---

Originally posted by slemson:

This solution will not work in a networked , shared solution will it? Won't everyone's portals change when one person changes the relationship criteria?

---

Hmm, good point. The only other solution is some kind of calculated multikey. You could either use specific dates, or calculate ranges (Past, Last Week, This Week, This Month, This Year, Future, etc) which is easier to do, but less precise.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[geod]

Go to www.databasepros.com. Scroll down to the section. "This site updated on..." then click the "new" graphic button. There are three Tip Files on filtering portals including by date. should get you on your way.

[Chuck]

If I'm not mistaken, you can get around the fact that the technique with the relationship based on the calculation not working by appending to the fields on both sides a global field that is unique to each user. The easiest way I can think to do this is to have a field gCurrent_User and set it in your open script to Status( CurrentUserName).

On the other side of the relationship, have some mechanism to keep a return deliminated list of the current users that need to be related to each record. The right side field in the relationship would have data like this:

1Chuck Ross

1Joe Smith

1Mike Jones

The left side would be a calculation something like this:

NumToText( Date1<=Date2 ) & gCurrent_User

That way, this calculation field will be unique for each user on the network. If the calculation results in 1Mike Jones, it will be related to the record with a field like the one above.

The only thing you need to figure out is how to get the return deliminated list to have the proper values. I'm not positive that this will work, but it's worth a shot.

Chuck