12 Replies Latest reply on May 24, 2013 10:25 AM by tnoll@sonic.net

    Match record numbers 0001 and 0001A-C?

    tnoll@sonic.net

      Title

      Match record numbers 0001 and 0001A-C?

      Post

           I need to consolidate 8 tables into one with the fields from all the tables on one layout. The creator of these tables had an 0001 naming scheme up to 1091 records but occasionally tacked on some letters as above. Record 0001 and 0001A-C are actually the same item in two seaprate tables but I don't know how to match those record numbers for importing one table into another (not sure if that's the right term for this action). I need to retain the A-C as it apparently indicates that item 0001 is made up of 3 compnents--A, B and C. Unfortunately the person's various tables relating to 0001 (and others) have some inconsistent record numbers like this. 

           Is there a way to get Filemaker to match on just the first four numbers and retain any subsequent letters in the record number? For example, I need to match 0001 from a classification table and 0001A-C from a materials table and 0001 from a table of photos of these items (historical artificial heart valves) and so on through the entire collection of over 1000. They didn't know how to organze from the start . . .

           Ideas appreciated,

           Terrie

            

        • 1. Re: Match record numbers 0001 and 0001A-C?
          tnoll@sonic.net

               PS the tables are like spreadsheets, one with about 15 fields, others have fewer and the only common thing is the record number so it coulld be 30 or 40 fields of info about each item in total, plus a picture of each.

                

               Terrie

          • 2. Re: Match record numbers 0001 and 0001A-C?
            philmodjunk

                 Is the value to be matched always a number and the "added on" portion always letters?

                 If so, a pair of calcualtion fields:

                 GetAsNumber(OriginalField) // Select Number as your result type

                 could be set up to strip out the non-numeric characters.

                 But that reference to A, B and C components suggest a one to many relationship may exist and that in turn argues that you should not merge this data into a single table.

            • 3. Re: Match record numbers 0001 and 0001A-C?
              tnoll@sonic.net

                   To your first question, yes. But I need to retain the letters while matching on the first four numbers.

                   When you say 'pair of fields,' I assume you mean I need to set up a calculation field in each of the tables being matched?

                   Don't think it's one to many issue; the original creator just added letters to some of the record numbers to show that the item being cataloged in the db was made out of three components A, B an C--but she didn't do it consistently. 0001A-C in one table is the same item as 0001 in another and needs to link up to the 0001 photo of the item. However the letters are item classifiers and need to be retained.

                    

              • 4. Re: Match record numbers 0001 and 0001A-C?
                philmodjunk

                     What I am suggesting is that you define new calcualtion fields that use GetAsNumber to stip off just the numeric portion of the text. This keeps your original fields unchanged so you do not lose the added letters.

                     Then use these calculation fields in your relationships to match just by number.

                • 5. Re: Match record numbers 0001 and 0001A-C?
                  tnoll@sonic.net

                       OK. Did a bit of research about calc fields. When you said 'strip off' I thought the letters would be removed. Haven't tried it yet, just on the verge, think it will work. Thanks very much.

                       Terrie

                  • 6. Re: Match record numbers 0001 and 0001A-C?
                    philmodjunk

                         yes, the letters should not be visible in the calculation field.

                    • 7. Re: Match record numbers 0001 and 0001A-C?
                      tnoll@sonic.net

                           If the calc field is going to return a result that is only a number, that isn't what I need. I need to combine the data from eight tables which all have different fields, whose only common field is an Accession number field. Unfortunately the creator used 0001 in one table and 0001A-C in another table but those Accession numbers are for the same item. I need to combine the 8 tables and their fields to one form view layout per record using the Accession number as the match--only it doesn't match. And I need to keep the letters.

                      • 8. Re: Match record numbers 0001 and 0001A-C?
                        tnoll@sonic.net

                             Needs to be a text field. This isn't a math computation. Left ( text ; numberOfCharacters ).

                        • 9. Re: Match record numbers 0001 and 0001A-C?
                          philmodjunk

                               If the part of the ID field that uniquely identifies thes records is only numeric--which is what you have confirmed is the case, a number data type should work just fine and without issues.

                          • 10. Re: Match record numbers 0001 and 0001A-C?
                            tnoll@sonic.net

                                 It doesn't seem to on a preliminary attempt but I ran out of time to play with it and think it through. Serious deadline right now writing an iPad manual for someone who is 86. 

                                 I have to confess I'm not sure how this is supposed to work. I create a calc field GetAsNumber [Accession Number field] in each of the 8 tables? This lets me match the Accession number fields by the digits while ignoring the letters but retainng them so I can add data from one table to another? (I have to create a new layout with all the fields from all the tables; the tables have no fields in common except this problematic Acccession number field.) But there will only be one Accession number field, so if one table has 0001 and another has 0001A-C, which one ends up in the field? Has to be tested. 

                            I'm not clear how putting the calc field there accomplishes this and will have to test it another time. A developer told me since the Accession number field is text is should use get as text and indicate the places, but no time to test.

                            • 11. Re: Match record numbers 0001 and 0001A-C?
                              philmodjunk
                                   

                                        I create a calc field GetAsNumber [Accession Number field] in each of the 8 tables? This lets me match the Accession number fields by the digits while ignoring the letters but retainng them so I can add data from one table to another?

                              Yes

                                   

                              (I have to create a new layout with all the fields from all the tables; the tables have no fields in common except this problematic Acccession number field.)

                              No/ This is not necessary.

                                   

                              But there will only be one Accession number field, so if one table has 0001 and another has 0001A-C, which one ends up in the field? Has to be tested.

                              Don't quite follow that. With your relationships now matching strictly by the numbers you can put either field or both on a given layout.

                              • 12. Re: Match record numbers 0001 and 0001A-C?
                                tnoll@sonic.net

                                     Very kind of you to stick with me.

                                (I have to create a new layout with all the fields:  Later I'll test the new layout issue. If the fields from one table don't exist in another, then you can't match fields to tell the stuff where to go. Maybe if they don't exist they create their own fields on import if you just use the arrow to tell them to import. Will check. Just seems tidier to give them a place to go.

                                      

                                     

                                But there will only be one Accession number field, so if one table has 0001 and another has 0001A-C, which one ends up in the field? Has to be tested.

                                Don't quite follow that. With your relationships now matching strictly by the numbers you can put either field or both on a given layout.

                                     If I can put both that would be excellent and perfect but if I'm matching one field to the other . . . oh I think I get it, the match fields don't import. More testing, next week.

                                     Thanks very much! I can do some things but not others. Some time ago I made a db to calc the value of my gold based on held ounces and current spot price and my profit based on cost and current price and now I don't even know how I did it : )

                                     Terrie