Jump to content

biiiink

Members
  • Content Count

    13
  • Joined

  • Last visited

Community Reputation

0 Neutral

About biiiink

  • Rank
    member

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

749 profile views
  1. I did also post this on the Filemaker forums here : https://community.filemaker.com/message/503303 I was slammed with work over the past week and just didn't have a chance to try out the suggestion. Also. He
  2. I have a relationship of: Clients ---< Policies ---< Renewals On the Renewals table, I have a field named "Employees" which tracks the number of employees a Client has. Renewals happen once a year for each policy. One client can have multiple policies, and as a result, multiple renewals a year. I need to be able to track the total number of employees each year, but since Clients with multiple Policies will have employees registered under both policies, it results in duplicates in the Employees field. For Example: Client A - Policy 1 - 2015 Renewal has 50 Employees Client A - Policy 2 - 2015 Renewal has 30 Employees Client B - Policy 1 - 2015 Renewal has 25 Employees Client C - Policy 1 - 2015 Renewal has 40 Employees Total employees for 2015 would be 115. Basically, I need a field that sums all the records but ignores the duplicates with the lower value in the employees field.
  3. biiiink

    Filtering Portal with Global Fields

    The screenshot is in the original post
  4. biiiink

    Filtering Portal with Global Fields

    The majority of clients will have only one policy, with one renewal per year that the policy has been in place. A few clients will have 2 policies. It would be rare that a client have 3 policies. I originally tried filtering the portal as you described above, but I still ran into the same problem as shown in the screenshot. Upon further testing, In the portal showing records from the renewals table, it seems that any fields that are from the policies table will only show information from the first policy record
  5. biiiink

    Filtering Portal with Global Fields

    It is the policy that gets renewed One Client can have multiple policies. Each policy will have multiple renewals. For example: If Client A has 2 Policies, and each policy has renewed 3 times, that would be a total of 6 renewal records that are related to Client A. I need a portal that shows all 6 renewal records on the Clients layout.
  6. My database has Relationships of:  Clients ----< Policies ----< Renewals  In a layout on the Clients table, I have a portal that shows all of the related Renewal records  I would also like to be able to filter the Portal with 3 different global fields.  The names of the fields I want to be able to filter by are: Year, Carrier Name and Status.  The problem (at least I think it is the problem) is that the Carrier and Status fields are stored in the Policies table  I attempted to create the Carrier filter.  I created g_CarrierFilter in the Clients table.  Created a new table instance of the Policies and Renewals tables named Policies_filter and Renewals_filter with the same relationships as above, except the  Clients --< Policies Relationship also has g_CarrierFilter x Carrier Name  I added the global field and created a value list from the Carrier Name field in the Policies table.  I switched the Portal and all of it fields over to the new Renewals_filter table instance  But what I'm getting is shown below:  The Carrier for the top record is correct, but the carrier for the second record should show as "RBC Insurance" and the 3rd should be "Empire Life" and if I select either of those as the filter, all of the records disappear.  I've double checked that the Carriers are correct in the renewals record.
  7. What I posted above seems to work for my purposes. I've put it in the Validate by Calculation section for the Email Field, but again, how do I allow the field to be empty and it not return an error? hmmm...I thought I tried that earlier and it didn't work, but it works now. I must have done something wrong the first time. Thanks for your help
  8. I guess im just looking to make sure that it has an "@" and a period in it. I found this through google, and it seems to work well: Let ([ L = Length ( Self ) ; // get length of eMail address At = Position ( Self ; "@" ; 1 ; 1 ) ; // Find at least one @ signred Period = Position ( Self ;"." ;L ; -1 ) ] ; // Find at least one period from a right to left search At > 1 and At < ( Period - 2 ) and Period < (L - 1) // Return boolean T/F if @ and period were found in a normal email range ) But how do I allow the field to be empty as well?
  9. Hello I have an email field in a Clients table that I would like to ensure has a valid email address to avoid any issues with typos and such I'm not quite sure how I would go about doing this Also, the field needs to be able to be empty as well
  10. biiiink

    Help with Relationship Diagram

    Thanks for your replies I currently have all of the possible lines of benefits as fields in the Policy table. I believe that the Policy-<Benefits relationship is a many to many relationship. One policy can have multiple benefits, and one benefit can be in multiple policies The Renewals table is basically what you were describing the Rates table to be I'll give an example to try an illustrate where Im getting confused. Client 1 has a new policy in 2013 with 2 classes (Class A, and Class B. and each Class has a their own Health rate. (class A = $10/month. Class B = $15/month) In 2014 when their policy renews, Class A's rate is now $15/month and Class B's rate is now $20/month. I need to be able to keep track of what each Class's rate was in 2013 and 2014. My thinking is that if the rates are stored in the Classes Table, I'd need a new record for each class every time the policy renews, yet the only thing that is (likely) changing is the rates. Currently, by my count, there will be roughly 80 fields (not including the rates fields) in the Classes table. This seems like a lot of redundant data to me The rate is determined by the Carrier. Rates are going to be manually entered into the database every year at renewal.
  11. Hello I'm pretty new to Filemaker.  I'm using FM 13 Advanced.  I've taken 2 courses on filemaker on Lynda.com.  I feel that I've got a good grasp on the application, but I'm certainly still new, and still learning  I am designing a database for my group insurance business.  I'm working on the Relationship Diagram, and I think I have most of it sorted out, but I've run into one problem.  I'll start by describing how the DB will work.  Each one of our Clients will have a minimum of one Policy.  Each Policy will be through an Insurance Carrier.  Each Policy will consist of the lines of benefits that the Client has chosen (ex. Life Insurance, Health, Dental, etc.)  Also each Policy will have a minimum of one Class.  Roughly 80% of all policies will only have one class. 18% will have 2-3 and 2% will have 4+.  Each Policy will have rates associated with them and a date that the policy renews.  Every time the Policy renews, the rates are likely to change.  99% of the time, the rates will be the same for every class in the policy, however, it is possible for each class to have its own rates.  We need the database to track the rate changes in the policy every year.  Also, the Clients are able to change the lines of benefits their Policy covers at any time (ex. changing the amount of coverage, adding new lines of benefits)  We also want the ability to take notes on our Clients and their policies. I've attached A diagram of what I have so far.  The issues Im having is with the Policies/Renewals/Classes relationship.  The biggest problem i'm having is where to store the rates, so that they can be tracked each year, and can also be different for each class.  Any help/advice/suggestions would be greatly appreciated.
  12. Welp. That was amazingly easier than I was thinking it was. Thanks for your help
  13. Hello I'm still fairly new to FM I'm building a database for my business. In the Clients table, I have a details view that shows me all of the information about them, including a portal that shows all of the policies that the client has in the Policies table. What I would like to do is have the Policies Portal hidden if the Client does not have a policy record in the Policies table. I'm not quite sure how to go about doing this. Any help would be appreciated
×

Important Information

By using this site, you agree to our Terms of Use.