Portal Filtering

[mike bell]

Using Filemaker Pro 9

I have a file with about 150,000 customer records.

I want to have a portal that shows me the states customers reside. Currently, the portal shows every records state. Then I would like to show the number of records per state.

Any advise would be much appreciated.

[mr_vodka]

Use a global text field with a value list of your states in your parent table. Modify your portal relationship to additionally key on this global field to your customer state field for your child TO as well.

As for your records per state, you can create a summary field in the customers table and then use a subsummary report grouped by state. You can delete out the body part since you only need the grouped figures not the the individual records.

[mike bell]

Thanks John,

I have tried you suggestion.

Created a new field called new_states, set as text, under Options/Storage I selected Global. Then under the Validation tab I selected "Member of value list: States. States is a value list based on the customer table and it shows state names (1 of each).

My relationship is set up Customer Table(state) -> Customer Table2(new_state)

The portal shows related records from Customer Table2 and the field in the portal is new_state, however it still shows all states. For instance "CA" shows up in the portal about 5000 times.

I'd like it to show 1 of each state abbreviation.

[mr_vodka]

Okay now it is clearer what you are looking for. Take a look at this thread. It is what is commonly referred to Ugo's Method on here.

http://fmforums.com/forum/showtopic.php?tid/157638

However, with as many as 50 (plus protectorates if needed), it doesnt make sense why you just dont create a subsummary report for this. Why the portal?

Edited April 15, 2008 by Guest
typo

[mike bell]

I didn't select a SumSummary because I have other data/information on the same layout.

Seems like there would be an easy way to show a state name only one time, kinda like in SQL where you use DISTINCT.

[comment]

Seems like there would be an easy way to show a state name only one time

There will be - once you add a table of states.

[mike bell]

Here's the deal.

We have customers from all over the U.S. and many foreign countries. Basically, it is a mailing list pulled from our POS system, we have 35 stores across the U.S.

I want to see what states are listed.

If I create a states table, which I did, then I would have to list every country in the world in order to catch each one. Other wise when I send out a direct mail piece, one might get sent to say Puerto Rico or Japan and I only want to hit were our store are located. So, by seeing the states I can Find by state and delete from the list.

I have a couple of other functions that look at store info on the same layout, including number of names per store, etc..

All I need is to have a portal list the states.

[comment]

To see a list of distinct states in a portal, use the "Ugo method" as suggested by John earlier. I am not convinced it's the best method to achieve what you want, but I don't understand your description very well. For example, you could use a value list based on the state field (a value list shows each value only once) to select the state to find.

[mr_vodka]

Here's the deal.

We have customers from all over the U.S. and many foreign countries. Basically, it is a mailing list pulled from our POS system, we have 35 stores across the U.S.

I want to see what states are listed.

If I create a states table, which I did, then I would have to list every country in the world in order to catch each one. Other wise when I send out a direct mail piece, one might get sent to say Puerto Rico or Japan and I only want to hit were our store are located. So, by seeing the states I can Find by state and delete from the list.

I have a couple of other functions that look at store info on the same layout, including number of names per store, etc..

All I need is to have a portal list the states.

What is the END result that you are trying to do. Forget for now the method of how you think it should happen... What do you want at the end?

Every subsequent post seems to either contradict an earlier post or add more confusion. I dont see why you are deleting anything out for a mailing list. I also dont see why you just dont find each set of record per state that you want and send out the mailing list that way. I dont even know why you need a portal at this point since it seems as though you want to only have a mailing list for a particular state each time anyway. As Michael pointed out earlier, a simple value list will give you the states of only your customer records.

Edited April 15, 2008 by Guest
added more

[mike bell]

Hey Mr. Vodka,

I thought the info given in the beginning was enough to get to the point, but as I am finding out, it's always best to give all info.

I'm trying to create a data cleansing program.

One that will remove customers with incorrect data in certain fields, ie: no address or no city.

I do agree that I do not need to delete records, probably just need to omit once a customers info has been cleansed.

Now for the state problem.

Since we have alot of locations and most Direct Mail pieces are sent with custom market information, I need to make sure that all address are in say Texas, for our Texas stores. Most mailings are also based on quantity per store.

This is where I need to see how many customers per store will get the mailings and they must be in Texas. If the store is in say, Boston, MA, then I need to encompass several states in the area.

So, having this info is really important when sorting and omitting customer data.

I hope this helps and thank you for all of your input thus far.