2 Replies Latest reply on Dec 14, 2009 8:56 AM by philmodjunk

    Design Problem? Re-posted



      Design Problem? Re-posted


      Phil, Thanks for your latest response... Still having issues with part of my design, but it might be how the data is being pulled into the work order. For what it's worth, the r-ship you illustrated is actually the same as mine, but I may have made it worse. I'll explain: Not sure why or if I really needed to design it this way, but when it was drawn out on paper, it seemed logical. The solution only has one purpose, and that is to generate a basic work order for a septic tank pumping service company. The work order simply records the property information where the tank is located, the person who requested the service, usually a real estate agent. 75% of all work orders will be billed through an escrow account. The program did work most of the time, but written long ago back in the FMP 4.1 era... To create a Work Order, you first started in the Contacts file, on that persons record (if existing, otherwise a new contact record was required before you could begin) Once on that contacts record, simply click on Create Work Order and it copied the contacts info into the Work Order file and off you went. A problem occured at times when a new Contact record was created, it would not copy the info into Work Orders, only the Record ID. A second issue that needed addressed was having to retype the Escrow info into the Work Order, even if they were a known company. So I decided to rewrite it the solution properly, and on paper I came up with 5 basic tables, Accounts (for company info), Contacts, Work Orders, and Services (the 5th table was just a menu/utility table). My reason for wanting to rewrite the solution was to simplify data input obviously, but so others could utilize it. Since I was the only one with any type to FM Pro exposure, I could band-aid whatever I needed to just to spit out the WO. Anyway - Since my TO's appear to be setup like your suggestion, what would you suggest would be the proper way to insert Contact (realtors info) and the other Contact (escrow officer's info, etc) into the Work Order record?

        • 1. Re: Design Problem? Re-posted
             --- Frustrated - I apologize my post is one long rediculous PARAGRAPH, but I've tried to post my post about 5 times, properly written, and after clicking POST, it comes out like this, lol
          • 2. Re: Design Problem? Re-posted

            I've had formatting fail to appear in the post also--don't know why--it only happens once in a while. When it does happen, I start clicking the preview button and adding additional returns after paragraphs until it previews correctly. Odd, but that might help.


            "Since my TO's appear to be setup like your suggestion, what would you suggest would be the proper way to insert Contact (realtors info) and the other Contact (escrow officer's info, etc) into the Work Order record?"

            If I remember your original thread correctly, I believe I've already posted how to do that. You need just two fields, on for contactID (realtors info) and one for ContactID (escrow officer's info) You need separate relationships to different TO's for each such ID field. Once you have that you can add fields from the RelatorInfo TO for the first contact and fields from the EscrowIfo TO for the second set of contact fields.


            There is another alternative. Define a join table WO_Contact that links many work order records to many contacts:


            Two fields in your Join table:

            WorkOrderID, ContactID



            Contacts::ContactID = WO_Contact::ContactID

            WorkOrders::WO_ID = WO_Contact::WO_ID (Enable "allow creation of records" for WO_Contact in this relationship)


            Place a portal to WO_Contact on your Work order layout formatted to display two rows. Place the appropriate Contact fields from Contacts in the portal row along with the WO_Contact::ContactID field (Formatted as a two column drop down or pop-up menu). Now you can select a contact in The portal's contact ID field and the remaining fields will display name/address data for that contact.


            Row one in your portal can be your Realtor contact and Row two can be your Escrow agent.


            The choice is yours:: Two ID fields with separate relationships or a Join table with a portal.