Joseph31 Posted November 6, 2008 Share Posted November 6, 2008 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? Link to comment Share on other sites More sharing options...
Ocean West Posted November 6, 2008 Share Posted November 6, 2008 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/ Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 6, 2008 Share Posted November 6, 2008 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. Link to comment Share on other sites More sharing options...
Joseph31 Posted November 7, 2008 Author Share Posted November 7, 2008 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 Link to comment Share on other sites More sharing options...
Joseph31 Posted November 10, 2008 Author Share Posted November 10, 2008 Can anyone show me how to do such a thing. Please see above post. Thank you, Joseph Link to comment Share on other sites More sharing options...
comment Posted November 10, 2008 Share Posted November 10, 2008 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. Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 11, 2008 Share Posted November 11, 2008 (edited) 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, 2008 by Guest Link to comment Share on other sites More sharing options...
Joseph31 Posted November 11, 2008 Author Share Posted November 11, 2008 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 Link to comment Share on other sites More sharing options...
Joseph31 Posted November 11, 2008 Author Share Posted November 11, 2008 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 Link to comment Share on other sites More sharing options...
comment Posted November 11, 2008 Share Posted November 11, 2008 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. Link to comment Share on other sites More sharing options...
Joseph31 Posted November 11, 2008 Author Share Posted November 11, 2008 Which global fields? Should I list in the state table. Link to comment Share on other sites More sharing options...
comment Posted November 11, 2008 Share Posted November 11, 2008 The global date fields that set the range. Are there any other? Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 11, 2008 Share Posted November 11, 2008 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 Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 11, 2008 Share Posted November 11, 2008 Comment is right about the globals being in States. I built an example and can email or upload if you like. Good luck. OS Link to comment Share on other sites More sharing options...
Joseph31 Posted November 12, 2008 Author Share Posted November 12, 2008 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 Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 13, 2008 Share Posted November 13, 2008 Here it is. I hope it helps. good luck States_Dates.fp7.zip Link to comment Share on other sites More sharing options...
Joseph31 Posted November 14, 2008 Author Share Posted November 14, 2008 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 Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 15, 2008 Share Posted November 15, 2008 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. Link to comment Share on other sites More sharing options...
Joseph31 Posted November 17, 2008 Author Share Posted November 17, 2008 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 Link to comment Share on other sites More sharing options...
Oldsneekers Posted November 19, 2008 Share Posted November 19, 2008 Will the policy table have seller information (eg: seller ID, Seller state)? If so this can make the relationship work. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5778 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now