Newbie - Relationship Headache

[BeachDave]

I have developed the attached system (u: Admin, p: "blank") so far with a little help from those on FM Forums. :

Things were going really well until I tried to add several new features. These essentially revolve around limiting what a user can choose from two pop-up menus after having selected a value from a first.

In the attached, on the Lease layout >> Property tab it shows three pop-up menus ... island, property and company (manager). The idea is for the user to choose an Island first and then have the values in the Property and Company pop-up menus change.

I know I do not have the relationships defined properly on the ERD (graph) as I am not even realting manager to island (I don't know how to do it ... many-to-many I think?) I really need help to try and make this work.

I'm new to FM and database design in general. I think I have reached my limit (in terms of knowledge, patience and hair!) so I'm am reaching out for help. Any would be most appreciated. Thanks in advance.

lease.zip

[Matt Klein]

I'd love to help you out. However, you need to remove all the validation settings on the fields in this database. I just downloaded the file and tried to check out the schema. As soon as I added a record and tried to go to the schema I got bombarded with validation errors. It was so bad I couldn't even exit FileMaker without Forcing my out.

Make a copy of the file, remove all the validation stuff and post that one.

[BeachDave]

Thank you so much. Attached is the file with the "Required" validations removed. If you're in there and notice anything else that I might have screwed-up, please don't hold back!

lease.zip

[Matt Klein]

Thank you so much. Attached is the file with the "Required" validations removed. If you're in there and notice anything else that I might have screwed-up, please don't hold back!

I don't know that it was screwed up if you don't want your users to leave out data. It might have been a little much is all. :

I attached an updated copy of your file here. I added a Table Occurence Group specifically for the pop-up logic. I tried not to change any of your existing stuff for the most part.

Let me know if you have any questions. You can email me directly if you wish or through this Topic.

lease.zip

[BeachDave]

Thanks. : Working great. I actually added a simple pop-up on the "admin manager" layout to define the island for each manager.

BUT when the ultimate end-users saw that it only let them pick one island per manager they told me that a manager can cover more than one island.

For example ... in the file you sent back, the McLaughlin manager covers St. Thomas, St. John and Grenada while the Jennifers manager only covers St. Maarten and St. Martin.

So I created a set of checkboxes that includes each island on the manager page. Doesn't work - some error based on the id being a number.

I need for the user to be able to choose either one island or multiple islands from the check boxes. I think the relationship has to change in the database - maybe to a many-to-many? Right now the Manager and Island tables are linked by "island id" which is a single number. Really not sure how this should all work.

I don't want to keep bothering you, but any help or suggestions would be greatly appreciated. Thanks.

[Matt Klein]

Doesn't work - some error based on the id being a number.

Change all your ID fields to Text fields instead of Number fields. Number fields only allow one value. What you are trying to do requires more than one value in the island id field. Once you change it to Text it will work fine.

Make sure you change the island id, manager id, and property id fields to text in all your tables otherwise you may have some relationship problems. The auto-enter serial numbers will still work in a text field. The only draw back is if you ever wanted to sort by one of the ID fields. As a number, 10 follows 9. As text, 10 follows 1. You can always pad the next value with "0" so you would enter "0010" as the next value. The zeroes will stay when the next value increments to "0011" etc.