2 Replies Latest reply on Sep 19, 2009 3:34 PM by cartz

    need help overcoming bad database design

    cartz

      Title

      need help overcoming bad database design

      Post

      Hi,

       

      I'm new to FileMaker Pro Advanced but have had experience with other databases sometime ago and am slowly coming up to speed. I have inherited a badly designed database that was originally imported I think from dbase.

       

      Anyway, I'm hoping someone can help with my problem:

       

      I have a table that where each record has some basic information and then 24 number fields containing various values. My problem is I somehow need to effectively 'sort' those 24 fields in each record in ascending order. That is, sort within the record (the other records are irrelevant for this purpose).

       

      I know it should have been designed with a single record for each of the 24 values but it wasn't.

       

      What I'm thinking is I need to add another 24 fields on each record that effectively ranks the original 24 fields. i.e. - if the field with the lowest value is field 22 then the first of my new 24 fields would contain the value 22 and so on.

       

      The other option is if I can somehow create a new table that breaks each record into 24 separate records to make things super simple.

       

      Can I achieve either of these options with FileMaker Pro Advanced? If so, how?

       

      Or is there some other solution?

       

      Thanks,

       

      cartz. 

        • 1. Re: need help overcoming bad database design
          comment_1
            

          It's not THAT difficult to split the values off to individual records in a child table. First, make sure you have a unique value identifying the parent (a serial ID works best for this). Then create the child table with three fields: ChildID, ParentID and Value.

           

          Now, either import the parent records 24 times (each time importing ParentID into ParentID and a different value field into Value), or define a calculation field with 24 repetitions, each returning one of the value fields, and import the ParentID and the calculation field into the child table, with the option to split repeating values into separate records.

          • 2. Re: need help overcoming bad database design
            cartz
               Perfect! Thank you very much.