1 2 Previous Next 29 Replies Latest reply on Mar 26, 2012 3:11 PM by keith

    Basic Relationship Query

    keith

      Title

      Basic Relationship Query

      Post

      FM11 on Mac

      I have a basic personal/small business DB which works OK but I would like to change the relationships which currently use Company Names as the match fields. I wish to change to numerical IDs so that if a Company name is changed it doesn't affect the relationship.

      I have manually changed to IDs and the relationship works fine. However, I'm unsure how to do this properly for new entries.

      For example, in an invoices file, I wish to select a Company Name which then automatically gives the correct ID to pull in the details from the Contacts File.

      I have watched the video http://www.filemaker.com/products/demos/#basic_relations which shows this but I still can't see how it is done, i.e. when ABC plc is selected the ID appears. It states that the ID cannot be seen as there is a pop up menu attached. 

      Can someone explain where the ID originates in the video and why does the ID not change to the Company name when this is selected.

      I'm sure this is very basic but I cannot find any info on it.

      Thanks

      Keith 

        • 1. Re: Basic Relationship Query
          philmodjunk

          It starts with how you define your value list when you open Manage | value list.

          You define your value list to list the IDs as Field 1 and the company name as field 2.

          If you do not want the ID number to be visible when the value list is deployed in a drop down list or pop up menu, select the check box at the bottom that says: "Show values only from second field"

          Then on your field, if you select pop up menu, the company name field will remain visible even when you exit the field. If you use a drop down list, you see only the names while making a selection, but the number appears when you exit the field.

          There are some layout tricks that can be used with drop down lists to show the company name and hide the ID number.

          • 2. Re: Basic Relationship Query
            keith

            Thanks so much for the quick and informative answer.

            Understood instantly and works perfectly - just what I wanted.

            Easy when you know!

            Thanks again

            Keith

            • 3. Re: Basic Relationship Query
              keith

              As I said, above is OK. However, going to a Related Record was not working properly but I think that I have fixed that now.

              It has made me realise that my basic knowledge leaves a bit to be desired with regard to Relationships and duplicate Tables, etc.

              For example, I have a simple Layout for Statements, displaying records from Statements with all the Fields to match. However, when I wish to make a new Relationship from Statements to Contacts, FM forces me to create Statements 2 due to other relationships. I just wish to be clear which, if any, Match or other Fields or the Layout itself have to be changed to Statements 2.

              What is the rule? Also can you suggest any info on this as i haven't been able to find any?.

              Thanks for any assistance.

              Keith

              • 4. Re: Basic Relationship Query
                philmodjunk

                Each box in manage | database | relationships is called a table occurrence. Each table you create on the tables tab can have as many "table occurrences" as are needed to define different relationships between the same two tables and to avoid a "cycle" (my term) where you could have more than one path from table A to table B--which would create an impossible situation if allowed to occur.

                Each occurrence can be seen as just a label to use to refer to its underlying "Data source table". You can see an occurrence's data source table by hovering the mouse over the upper left corner. Naming conventions and color coding are often used to help spot all occurrences of the same table.

                Here is a tutorial link on the topic: Tutorial: What are Table Occurrences?

                • 5. Re: Basic Relationship Query
                  keith

                  Thanks for above  I am getting there! I have tidied up my manage | database | relationships - from grey spaghetti with meaningless names to neat coloured TOs with relevant names.

                  My initial query was due to my need to match with IDs rather than names.

                  Obviously I now wish to respect and maintain IDs which are set to Auto Enter and increment for each new record on all files. However with my Statements, I import Bank Statements details each month from another program but this entails importing all records (incl those already in FM), omitting the new ones and deleting the the others. This is simple and works well - initially I did try FMs automatic import but had problems and reverted to a manual method.

                  However deleting records leaves gaps in my IDs. To avoid this I changed Statement IDs to auto enter with the calc 'Get ( RecordNumber ) + 1'.

                  My question is whether this method is acceptable?

                  Thanks

                  Keith 

                  • 6. Re: Basic Relationship Query
                    philmodjunk

                    Why are gaps in the ID number an issue? It's pretty common to get them with serial numbers and a table where records get deleted from time to time.

                    Get (RecordNumber) returns the number marking the record's position in the found set. Thus changing the sort order, omittting records, performing finds, etc. will change the value returned by Get ( RecordNumber  for a given record. Given that imported records from a found set of just the imported records, I don't think your auto-entered calculation will work.

                    If you really, truly must have no gaps in your serial number series, you can import your records, drop out the duplicates, sort them the way you want them and then use Replace Field contents toa assign a serial number (and update the serial number settings at the same time).

                    BTW, if there's a field or combination of fields that uniquely identifies each imported record, you can use field validation to screen out duplicates during the import process.

                    • 7. Re: Basic Relationship Query
                      keith

                      Thanks for prompt comments.

                      Sorry, I did mean 'Get ( TotalRecordCount ) + 1'. 

                      I do prefer IDs with no gaps but I will have a play with the points you make.

                      Keith

                      • 8. Re: Basic Relationship Query
                        philmodjunk

                        Get ( totalRecordCount ) + 1 would assign the same value to each record. If there are 3000 records in the table this expression will return 3001 for each and every record in your table.

                        • 9. Re: Basic Relationship Query
                          keith

                          Thanks but I probably didn't make it clear.

                          It is set as an auto enter calc for a new record, i.e. not a calc field.

                          Thanks

                          Keith

                          • 10. Re: Basic Relationship Query
                            philmodjunk

                            In which case you still get gaps when you delete your duplicates.

                            • 11. Re: Basic Relationship Query
                              keith

                              Good point! It seems to work because all my imports are already sorted so that most recent records are first and therefore take the next IDs. I then delete the rest, e.g. 500 current FM records with 10 imported (5 new and 5 dups) - the 5 new ones take 501 to 505 and the dups take 506 to 510. I dump the dups leaving the next available ID as 506.

                              Hope this makes sense - although may not be best way of doing it.

                              Thanks

                              Keith

                              • 12. Re: Basic Relationship Query
                                philmodjunk

                                dummy post to see 11th message.

                                • 13. Re: Basic Relationship Query
                                  philmodjunk

                                  It does and should work if this is always the case and if you do not have a multiuser system where another user might add records to the table at the same time your run your script.

                                  • 14. Re: Basic Relationship Query
                                    keith

                                    Yes, there is only me to mess it up. Can't blame anyone else.

                                    Thanks

                                    Keith

                                    1 2 Previous Next