3 Replies Latest reply on Apr 29, 2009 10:21 AM by philmodjunk

    Search dilemma



      Search dilemma


      Hi all,


      I'm fairly new to FM so please bear with me.


      Basically, I've set up a small 4 table database for my company. The tables are 'Candidate', 'Client', 'Jobs', & 'Staff'. The tables are all related to the staff table through the 'initials' field. (IE, when someone adds a new client, they select their initials from the list & the client becomes 'theirs'. The same is true for candidate & jobs).


      What I'm trying to achieve is a script that enables me to go into the staff layout, select the appropriate member of staff and off their personal record page, run a search that displays (in a formatted results page) all candidates, clients & jobs they've added to the database between a set of dates.


      However, whilst having a basic knowledge of SQL (specifically mySQL/PHP etc), I'm struggling with FM. Any idea where to begin?


      For your info, I'm using FM 10 Pro.


      Thanks for your help.


        • 1. Re: Search dilemma

          If you haven't worked with a portal before, look up the details in your on line help. Using a layout that refers to the staff table, you can add a portal or portals to display related information. THen you just need to find the desired Staff record to see the records they've added. You can do this find by hand or in a script. With FMP 10, you can even list your staff in a drop down menu and use a script trigger to perform your script to find the correct staff record.


          That approach is best for data entry.


          If you want a summary report, you can perform finds on layout referring to one of the other tables.



          To find all "jobs" added by staff person "Jane Doe", create a layout that refers to jobs. You can also had name and other fields from the staff table to this layout because of the relationship you've established. If you perform a find for "Jane Doe" (Or her initials), you'll have a report listing just the Jobs added by her and know one else.


          Design note: I wouldn't use initials as the key field for my staff records if I were you. If Jane Doe marries a Mr. Smith, changing her initials in the staff table to JS instead of JD will break the connection to all the other tables for her records. Instead, define a serial number field in Staff and use it as the key field linking your tables. (You'll need a matching number field in each of the other tables.) Then, name/initial changes won't create this problem.

          • 2. Re: Search dilemma

            Thanks Phil.


            I could, if needs be, simply search the initials & date created on each table layout, which I think is similar to what you're suggesting?


            However, this is more time consuming than I'd like really, hence why i'd prefer to script a more automated function that draws the data from all 3 tables at once instead of having to do seperately for each table.


            Or am I deluding myself and it's not possible?

            • 3. Re: Search dilemma

              I'm assuming you want a printed report rather than a data entry layout or a layout that is simply used to provide an overview of all the related records on your computer screen. If my assumption is wrong, then just put a portal for each related table on a layout that refers to your "staff" table.


              This doesn't sound easy to do. Much depends on the structure of your tables and the format of the report you are trying to create. It sounds like you want a report thats divided into several distinct sections, with each section listing records from a different table. It's possible to do that by setting up a layout referring to staff and placing extra large portals, one for each related table, on your layout and formatting them to slide up and shrink. The problem you run into, however, is that if you ever get more related records than your portal is tall enough to show, some portal rows will be left out of your report. (You can set a portal to shrink, but you can't set it to grow.)


              The alternatives can get complicated and messy, but it can be done.


              Describe what you want in more detail. If we have to go the "messy" route, I'll give you a general description of a method and maybe another forum member will have a better way that they can share.