1 2 3 Previous Next 136 Replies Latest reply on Jun 5, 2012 2:36 PM by hwminc

    NEED HELP! Table Occurrence issue...I think...



      NEED HELP! Table Occurrence issue...I think...


      Wondering if someone can help me out. I only have a few more functional items to add to a database I'm working on, but am stuck on this one issue. 

      I have two different table occurrences - each for a different 'kind' of contact, Media or Client. What I am trying to do is set up a 'Task' layout that will allow me to go from the Media Contact layout and automatically populate the Media Contact Name field when creating a new 'Task' so I don't have to retype all the info from the Media Contacts layout...

      I have the two tables occurrences Media 2 and Client 2 linked to the Task Layout via the fields I want to populate automatically in the Task layout when creating a new Task. Just not sure how to get the fields to populate correctly! Any help would be greatly appreciated!

        • 1. Re: NEED HELP! Table Occurrence issue...I think...

          You say you have two table occurences. Are they based on the same table?

          Or on different tables?

          I would create one table that contains contacts and I would give them a field "Type" where you can then set "Media" or "Client"

          Unless they are two very different things that you want to keep separated. I for instance have, in my solution, a clients table and an employees table. They are both contacts, but they are different enough to warrant two different tables.

          I recently made a little example file for someone that employs the technique of showing people's adresses in a portal. And it has a "switcher" where you can choose to see either the business adress of the shipping adress.

          If you want you could take a look at that to see how it's done:


          Otherwise, give us some more info on your tables and your relationships. That way we can help you a little better.

          • 2. Re: NEED HELP! Table Occurrence issue...I think...

            Dear DeSaint,


            Thanks so much for your reply. Yeah, initially I had tried setting up the three different types of contacts I work with (Media, Clients, Prospects) as one 'contact' with a dropdown that would let me pick which was which, but the further I got, the more I realized just how different all three are. The Prospect contacts for example, have nothing whatsoever to do with the other two, and Media and Clients are pretty heavily related as I have to be able to track Media activities for a particular client always. So, I decided to keep it easy in terms of navigation in the db, to just create layouts based on each type of contact and then have link buttons to every other type. I don't know if there is a way to do what you suggest where there is one table for all three and then have it set up so based on the 'type' of contact it is, the layout automatically changes? I could see how that might work better...a little :-)

            So to answer your question, what I tried to do was create another table occurence of Media Contacts & another one for Clients, so I now have the original tables plus a table occurrence for each one titled 'Media Contacts 2' & 'Clients 2' respectively. From there, I created a 'Task' table that has fields from both the table occrrences I created. problem is, I can't figure out how to get the Media Contact Name, Media Contact Company & Client Company fields I have in the Task layout to enter the info from the Media Contact I was just on so there is no need to retype info into those two fields. I just want to be able to hit a button and have the info from that contact in the fields automatically and then create a script that will give me a dropdown or popup menu of the Clients that are designated as 'Active' only...

            Thanks for any help you can provide!

            • 3. Re: NEED HELP! Table Occurrence issue...I think...

              The error you made was making a table occurrence for the other types of contacts.

              If you create a new table occurence you don't have an extra table. You stil only have one table.

              Table occurences are only used to be able to create different kinds of relationships.


              So if your goal is to have different tables for the different kinds of contacts (which could very well be a good idea)

              You will need to crate two additional tables.

              I suggest you keep your names "Media" - "Clients" - "Prospects"

              Then, because you probably already have a lot of contacts in your original table you will need to do some importing to get the correct contacts in the correct tables.

              Then in your tasks table you create three Foreign key fields (normal number fields)


              And you relate them to the ID field of the correct table.

              Then on your tasks layout you can set dropdowns on each type of contact and you'll only see the contacts from that table.

              If you want, on the contacts layout you could place a button "crete new task" that sets the Contact Id as a variable, and then goes to the tasks layout and sets the Id in the correct Fk field.

              Also, if you already have a layout for your contacts, you can just copy it and link it to another table.

              • 4. Re: NEED HELP! Table Occurrence issue...I think...

                Dear DeSaint,

                Thanks so much. Here is a picture of the relationships graph I have thus far. Hope this clarifies it a bit?

                • 5. Re: NEED HELP! Table Occurrence issue...I think...

                  How many actual tables do you have, and what are they?

                  I can't really tell on what tables all these TO's are based on.

                  I'm guessing I'm seing 4 TO's that are all based on the same table (Clients)


                  But like I said, if you want to separate your three different kinds of contacts you need to create three different tables.

                  It's your choice, and you don't have to. It depends on the way you work with these different kinds of contacts.


                  If you want to keep them all in the same table you will need to add relationships that are based on the "type" field.

                  A question:

                  If you go to a layout based on "HMW Clients" and you go to a list view you will see all the records. For the Clients, for the Prospects and for the "Media contacts."

                  You could have buttons or a scripted find that finds you only one type of contacts; But that's something you don't have if you put them in different tables.

                  Then you can just show a list view with all the records and you'll see only the "prospects" for instance.


                  So you need to decide first how you want to store your contacts.
                  - One table and a "type"
                  - or three tables.


                  • 6. Re: NEED HELP! Table Occurrence issue...I think...

                    Sorry bout that...here is a list of the tables I have. The Contacts are separated by the tables Media Contacts, Clients & Prospects. Then I made two table occurrences, one being Media Contacts 2 and the other is Clients 2 and then related those to the Tasks table, but it didn't work as I expected it to when I was trying to populate the relative fields in my Tasks layout (Form view) where the layout is comprised of fields from both table occurrences I created...

                    Because the information for each type of contact is so different in all three cases, ie - they all will have some commonality when it comes to address, company field, phone, fax, email web, etc - but then there are additional fields that are absolutely specific to the type of contact, so I set them up as separate tables because I thought that would clarify it more when I am running and gunning. Is there a way to jump to a layout that is specific to the type of contact based on nothing other than a type field? That might actually work. I just wanted to clarify that when I am in Prospects table, there is no relation to the Client or Media tables at all, but the Client and Media tables will need to have some interaction for things like Press release activity and Media Activity.

                    • 7. Re: NEED HELP! Table Occurrence issue...I think...

                      Allright, perfect.

                      Ok, here's what to do:

                      Don't forget to give your tasks table it's own Id  -  A number field with auto enter serial value! And make sure any existing tasks get a correct serial number.

                      Create three number fields in your tasks table.

                      Call them:
                      - ClientsIdFk
                      - ProspectsIdfk
                      - MediaContactsIdFk

                      Then create the following relationships:

                      Tasks::ClientsIdFk------[=]-------HWM Clients 2::ClientId
                      Tasks::MediaContactsIdFkdFk------[=]-------HWM Media Contacts 2::ContactId

                      Put these three number fields on your task layout and create a dropdown value list for each of them.

                      The valuelist is from the corresponding linked table (ClientsIdFk get's a valuelist based on the table HWM Clients 2
                      And the first row is the ID field (HWM Clients 2::ClientId)
                      The second row is the Contact Name from that table.

                      If you want, in the value list settings you can set it to "only show second row"

                      Then you start adding fields from the three tables to your tasks layout. And if you make a selection in your dropdown list, the correct fields will be filled in.

                      The annoying thing here is that you have three times the same fields.

                      Alternatively you can put three merge fields over each other on the same spot. But than you can't change the data.

                      If it's only for display this might work.

                      • 8. Re: NEED HELP! Table Occurrence issue...I think...

                        I just noticed that your HWM Clients table has a lot of relationships based on the Client CO Name field. That's a bad idea.

                        Use the ID field in stead. Make sure the ID field is a number field set to "Auto enter" using a "Serial Number"

                        • 9. Re: NEED HELP! Table Occurrence issue...I think...

                          If you don't like the idea of three different sets of fields on the contacts table, you can always still go back to one table with a "type" field.

                          Then you could also have three dropdown boxes for each "type" but the fields would all come from the same table, and that might be in some occasions a little handier.

                          It's something you need to decide for yourself.

                          • 10. Re: NEED HELP! Table Occurrence issue...I think...

                            Relationships based on the Client Co Name field... I have the ID fields in each table set up so it designates also what type of contact, so the serial number looks like this for Media Contacts (MC0001), Prospects (P0001) Client (C0001) - i thought it would be easier to discern that way for some reason, but am now wondering if I need to just go with one overall 'Contacts' table and then have the thing jump to whatever layout is appropriate for that particular type of contact? Don't know if that is possible...i just need a specific layout setup for a particular type of client I am working in, but maybe with some additional design thought I can accomplish the same thing from a single layout...

                            So each table should be related to another table based on an ID field, as opposed to something like the Contact Company Name field, eh? I guess that was what I was reading about in that Filemaker Missing Manual I got a while back! :-) I think it is getting clearer and I sincerely appreciate you unwavering help!

                            Okay, got the three number fields set up in the task table, so now for the relationships between the Press Activity Table, Media Contacts Table and Client Contacts Table, the Press Activity Table (the table which is essentially the 'join' point, needs to have its own ID field, right? Same thing for the Media Activity Table when joining?

                            When I am setting up the Tasks layout, how do I get the Media Contact Name and Media Contact Company fields to auto-populate themselves with the info from the Media Contact i was on in the Media Contacts layout? I was thinking that I could write a script somehow, but then can't seem to figure out how to trigger it once I am on the Task layout...

                            • 11. Re: NEED HELP! Table Occurrence issue...I think...

                              Ah, If CO name contains a Unique number it's better. But because it said "Name" I was afraid you had used an actual Name.

                              You can use this kind of calculation but be carefull, when this client becomes a different Type. When a Prospect becomes a Client (I don't know if that's possible) you have a big problem. Because all of a sudden you have a bunch of records pounting to P0001, but that prospect doesn't exist anymore. It has become a C0001.

                              So I would suggest to:

                              Give Every table a Number field called "Id" with an Auto enter serial number value. It's just good practice.

                              You added the type you you can see by the CO Name what type of client it is, But I would never show a client ID to a user anywhere. I would always hide it.

                              ID's are something a user need never see :)

                              You can however add the actual Type "Prospect" when you are displaying client information.

                              That way it's a little neater.

                              By the way, Now that I'm looking at your other tables, I'm still seing a lot of tables that don't have an ID field. (Media Activity) (Press Release Activity) (Client Documents) (And tasks in that screenshot  :p  )
                              Looks like someone didn't pay attention while reading that "Missing Manual"  :)

                              Also for the related contacts you might want to create a "Join" table between the Clients table and a second table occurence of Clients.


                              When I am setting up the Tasks layout, how do I get the Media Contact Name and Media Contact Company fields to auto-populate themselves with the info from the Media Contact i was on in the Media Contacts layout? I was thinking that I could write a script somehow, but then can't seem to figure out how to trigger it once I am on the Task layout...

                              I kind of explained that three posts ago. The idea is to set the ID of the Media Contacts in the MediaContactsIdFk field of the tasks table. You can do that either by putting a dropdown value list on the tasks layout. Or by putting a script on the Media Contacts layout that sets the Media Contact's Id as a variable and then goes to the tasks table (and maybe creates a new record??) and sets that variable in the Media ContactsIdFk field.

                              On your Tasks layout you put the fields you want to see from the Media Contacs table (Name) (Company).

                              • 12. Re: NEED HELP! Table Occurrence issue...I think...

                                To give you an idea of how this would work with all of your contacts in one table try this little demo:


                                You store all contacts in the same table, give them a type. Then in the tasks layout you select a type and then only those contacts are visible.

                                • 13. Re: NEED HELP! Table Occurrence issue...I think...

                                  So here's the problem...I put the fields I want to see from Media Contacts table into the Task Layout along with the fields I need from the Task table. It looks like the Task layout is just re-creating the information on a record by record basis from the Media Contacts table (essentailly just copying the Media Contacts table record by record), which isn't what I want. I need to be able to pull the relevant data from a Media Contact that I'm looking at in the Media Contacts Layout, by clicking a button titled 'New Task' and have the Media Contact Name and Company Name info show up in the Task Layout. I assume that if i delete a Task it will now also delete info from that Media Contact from what I can tell?

                                  Seems like I am close though, so appreciate your patience!! I will try to go the Set Variable route, but right now, the Media contact info is just getting copied for every MC record...

                                  • 14. Re: NEED HELP! Table Occurrence issue...I think...

                                    If you are afraid to delete related records go and look at the "settings" of the relationship and see if "Delete related records..." is checked:

                                    Sometimes that is nessecary. For instance, when that contact is deleted, do you still need his tasks?

                                    I'm sure you don't want to delete the contact when this task is deleted.

                                    So do not set the "Delete" on the contact side, but you might want to set is on the "Tasks" side. But that depends on you.


                                    1 2 3 Previous Next