2 Replies Latest reply on Sep 28, 2011 10:29 AM by JeffDening

    A sorting/ordering problem



      A sorting/ordering problem


      I use Filemaker for the business tracking of my musical instrument repair business.  I created a layout to track instruments I have in the shop where the instrument is checked in with the date being automatically entered (but I can change if neeed).

      One problem I am having (annoyance more than anything) is some instruments have come through the shop more than once--i.e. flute comes in back in June for emergency gotta have it now work but now is back for more thorough going over.  The other tables linked to the check in table reference the June date and when I look at the whole list showing active in the shop instruments it shows both the June and the now entry as being in the shop.

      I am wondering how to go about tweaking this so I can have individual check in records for multiple occurrences of the same instrument.  I can provide details as needed but I don't want to spell out my entire architecture if that amount of detail is not needed for a solution.  I do have a serialized ID for each check in item but at this point it is not used for anything other than a placeholder.




        • 1. Re: A sorting/ordering problem

          It is better to provide too much detail than not enough. We can always skiim over the extra detail, but have to ask you to provide the missing details before we can then offer a solution...

          I think this is the heart of the matter:

           I do have a serialized ID for each check in item but at this point it is not used for anything other than a placeholder.

          This is field is your primary key and should be used to link a record in your instrument table to records in related tables. Since you aren't using that field, you already are set up for problems here as other fields used for this purpose can be very problematic.

          I can't suggest much beyond that without knowing more about your databse. Try listing the tables you use for instrument repair and describe how they are related to each other. You may also need to describe the design of your layout.

          Here's a simple format for describing the relationships you've set up in Manage | database | relationships:

          Instruments::__pk_InstrumentID = Repairs::_fk_InstrumentID

          Instruments and Repairs would be two of the "boxes" on the relationships tab. __pk_InstrumentID would be an auto-entered serial number and _fk_InsturmentId would be a number field and these two fields are used as match fields in this relationship. (__pk is a naming convention you can use to identify a "primary key" and _fk is a convention for identifying a "foreign key".)

          • 2. Re: A sorting/ordering problem

            Thanks Phil

            I will try to list more detail soon.