3 Replies Latest reply on Nov 22, 2011 8:55 AM by PaulWebb

    Working with data



      Working with data


      Let me start by saying I'm a baby developer. Only 2 wks old and still working with my trial version.

      Before the appropriate group will give me read access to the DB that houses all data I am having to use an excel file to import my data. The data is trouble tickets and all supporting info. The data I am having trouble with is in regards to hardware returns called RMA. Each RMA has a unique number tied to it. The problem I am having is if an RMA has a field with multiple values it is repeated over several rows in the excel file. I was successful in creating a portal to show unique RMA numbers but now I am trying to show all related data.

      Example data

      RMA # Part Serial #
      1234 PWR Block PWR678
      1234 Fan ---
      9876 Blade bl34gh

      I have a small excel file if it helps or I can provide a screen shot of the data. Ay help, recommendaions, websites, books, training... would be appreciated.


        • 1. Re: Working with data

          Hi Paul,

          You need two tables:

          RMA table (holds one each of your REturn Material Authorizations)
          RMA (holds one each of the unique RMA number)

          Trouble Tickets table
          ... what you currently have

          You then relate them on RMA using =

          Create the RMA table first then select Options and go to Validation tab.  Set it to validate 'unique' and at top, specify 'always' and uncheck 'allow user override.'   Now go to a layout based upon Trouble Tickets and show all records.  Then switch to a layout based upon RMA and import, mapping RMA from Trouble Tickets to RMA in RMA table.  Specify ADD only which will create one unique RMA in the parent (RMA) table.

          • 2. Re: Working with data

            Now for a bit of basics:

            ALL tables should have a unique, auto-enter, FM-generated serial number and THAT should be used as the keys between all of your relationships.  Because you need to establish the relationship based upon RMA, I have described using it as the key.  Once your migration is complete, it is highly suggested that, before going live, you switch to true IDs.

            You might ask why not just use the RMA number since it is unique?  Well, you can.  If, when you go live with this system, the RMA will begin to be generated within FileMaker, you can have the auto-enter serial continue your numbering.  Just put your highest RMA number (plus 1) in the auto-enter serial.  But if this RMA comes from an outside source, you cannot trust your relationships to data that you cannot guarantee will be unique.

            If you wish to switch to RMAID (auto-enter serial) instead of using the RMA itself, create your auto-enter in RMA, show all records, then Replace Field Contents by 'serial' and be sure 'update auto-enter' is checked.  This is will serialize your RMAs.  Then simply add an RMAID field into Trouble Tickets, show all records in Tickets, place cursor in the Ticket's RMAID field and then Replace Field Contents by calculation with:


            Then change in your relational graph from using RMA to using RMAID.  Back up first.  :-)

            • 3. Re: Working with data

              Thanks LaRetta. Had a feeling I would need two tables for the data.