2 Replies Latest reply on Jan 7, 2010 3:41 PM by sedona_1

    Initial Design Problem



      Initial Design Problem


      Hi All!


      I'm brand new to FileMaker - using FM Pro 10 with Win XP Pro SP3. I'm stuck at the very beginning of my database design with an issue that really shouldn't be very hard to resolve. For some reason, tho, I can't seem to get past it!


      I work for a flooring subcontractor, and the database I'm creating is for the purpose of tracking individual jobs. As part of my solution, I have a Customers table for info on the entities we work for, and one thing I need to be able to do is sort, report, etc. on the customer name. My problem is that a "customer" can be either a homeowner (with a first and last name - last name first) or a general contractor (simply with a company name). I've created a "name" field for this data, and I'm wondering about entering the name of a homeowner like "Simpson, Homer". Will having that comma separating the two names create any problems down the line - with scripts, for example, or even with simply sorting on that field?


      Since the majority of our work is commercial (with a general contractor), rather than residential, I initially thought of, perhaps, creating fields for "company name", "first name", "last name", and "CUSTOMER NAME" - with CUSTOMER NAME being based on a calculation or script. In other words, if the "company name" is empty, I'd have FM concatenate and use the data in "last name" and "first name" to populate CUSTOMER NAME - otherwise, to simply use the data in "company name". While I was pretty proud of myself for thinking of this (as I've never used any form of scripting before :smileyhappy:!), I do want to keep things as simple as possible. What do you recommend?


      (Please note that I do realize general contractors are companies and homeowners are people - two different types of entity - but, for my purposes, there's absolutely no reason to have separate tables for them. In fact, that would probably complicate the issue even further.)


      Thanks so much for any help you can provide!

        • 1. Re: Initial Design Problem

          Well, both options should work well for you. You'll just have to decide which you like best.


          Here's a calculation field to merge Company, First and Last names into the format you describe:


          If ( IsEmpty(Company) ; Company ; LastName & ", " & FirstName)

          • 2. Re: Initial Design Problem

            WOW! Talk about a fast response!


            I'm going to go with the individual fields and use the calculation you provided for me. That way, if I ever need to use the names separately at some point down the line (in an email or something), I'll be ready.


            Thanks so VERY much!