Need some help.

Setup:

Welcome_page::State_Name x States_page::State_Name

I have a portal on the welcome page that list or counts all of the records in the database for each state. The relationship between the two tables is not (equals but Does not Equal) This way it shows all the states not just one state.

California 10

New York 9

Etc...

This works Perfect...

THE PROBLEM:

I would like to make this portal list only the records through a certain time period (a range of dates). For all States.

This would be Easy if the date I wanted to sort was on the States table but it is not it is on a different table not associated with this portal relationship.

Help

How can I do this?

Sounds like a job for Agnes' Custom List custom function and perhaps Bruce's in memory database technique...

http://www.briandunning.com/cf/868

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

I did something similar with an appointment book. The dates and times belong to one Table (dates). A second Table (appointments) has has the Staffer, client and date and times. I can filter the portal using both the names and dates even though the Date table does not store the staffer etc.

Make a global field in the State table where you will place the date (or the time period you want to filter by), let call this gTimeFilter.

Make a relationship between the states and the table with the dates (assuming they DO have a common match field entry such as STATE) using, as mach fields, the state names and the above mentioned gTImeFilter to the date table date field.

Place the field gTImeFilter on your layout, but for aesthetics, not in the portal. Thus the records will list in the portal if they match the State name and tiem time period.

I hope I was clear.

Good luck.

Wow -- it made sense I think -- So I tried it but nothing happened. It still shows the same count per state. Meaning it's not taking the date range into account. Can you show me an example of this?

Thank you,

Joseph

Can anyone show me how to do such a thing. Please see above post.

Thank you,

Joseph

This would be Easy if the date I wanted to sort was on the States table but it is not it is on a different table not associated with this portal relationship.

It would be helpful to know how the table with the date is related to the States table.

I hope this helps.

DateTable:

DateField

GlobalStateField

StateTable

DateField

StateField

ClientName

GUI table:

GlobalMaxField

GlobalMinIField

Relationships:

A) DateTable-GlobalStateField== StateTable-StateField

Gui-GlobalMax> DateTable-DateField &

Gui-GlobalMimi< DateTable-DateField &

Layout (formated to GuiTable)

Has portal: DateTable

Above the portal (or beside, where ever but not in the portal) are the fields Gui-GlobalMax, Gui-GlobalMimi , DateTable-GlobalStateField.

Inside the DateTable portal is the StateTable-StateField (since they are related one table will list the contents of the other table.

good luck

Edited November 11, 200817 yr by Guest

Thank you Let I will look at this and try it our... This will really help me if I can get this to work.

Thank you

Ok I have tried to use your example to use on my database. I keep running into setup problems.

My Tables

Welcome_Page:

Where I want to List the portal that sorts

GlobalMaxField

GlobalMinField

Policy_Page:

Policy_State (State where the Policy is Purchased)

DateField (Date which I need Sorted)

States_Table:

State_Name

State_Abv

Count_States (Count Function - Counts No of policies per state)

Using your example where and how should I put the relationships to make the portal that I need on the welcome page sort by each state and by a date range)

Thank you,

Joseph

The global fields should be in the States table. Then you can define a relationship between States and Policies, filtered by date (you'd probably want to use a second occurrence of Policies for this), and count the related policies from the point-of-view of States.

Which global fields? Should I list in the state table.

The global date fields that set the range. Are there any other?

In my example earlier I considered that there are many date field records but there is only one record for each state in the Sate table, is this right? My impression is that you want to show a portal that will list 52 states and the number of policies inside each state(from one Table) but filter the list by a given time period recorded by Date and State in another Table). I built a simple example and would up load it as an attachment, if I knew where the upload button was.

OS

Comment is right about the globals being in States. I built an example and can email or upload if you like.

Good luck.

OS

You got it -- Thank you for understand my crappy explanation of what i need. If it would not be too much trouble can you upload? This really helps me.

Thank you,

Joseph

Here it is. I hope it helps.

good luck

States_Dates.fp7.zip

Thank you for the upload...

I have made a slight error in my setup to you... So I changed your example to show you what I have --- The State is not on the Policy_Page but on a separate Sellers_Page. (See example).

I do not know if this affects the working of your relationships but mine does not seems to work. I have checked this over about 1000 times to mine... But no GO.

Any thoughts.

Thank you

for your time this is really going to help me.

States_Dates_Revised.zip

In the zip you sent back to me the portal on the left (pink) is formated for the TO Policy_page but the Count field is from the unrelated States_page. Look at the relationship diagram so see that your States_page is unrelated.

A portal displaying data from another TO must be related to that other TO. for two TO's to be related they must share some sort of data. Your Policy_Page TO has Date but nothing else to match with the other TO.

Now the portal on the right (red) is a portal from TO State_page2 and fields from Pollicy_page2. Looking at the relationship diagram these two are related only by the contents of the globals gMin and gMax in States_page2. As long as Seller_page only has State data. and Policy_page has only Date data there is no way to relate the two.

If you want to relate Seller_page::StateFields to Pollicy_pag::DateField you can't with the data is it is. There has to be data within one of the two that is common to the other.

good luck.

Thank you -- Just was checking to see if I was missing something.

The problem is that the states page defines the states for the seller. Meaning it has some compliance regarding the state.

The Seller page has all of the related seller information, Name Address etc...

The policy page has the policy information -- Date of policies Policy Number. From this page you add different sellers, brokers and information with related tables.

Is there any way i can get your example to work with these tables?

Thank you,

Joseph

Will the policy table have seller information (eg: seller ID, Seller state)? If so this can make the relationship work.