13 Replies Latest reply on Dec 16, 2014 11:58 AM by gremlin9297

    Trying to create a Message and Phone log



      Trying to create a Message and Phone log


      Im trying to create a solution that will keep track of both phone calls and phone messages. Let me try to explain what im trying to do. I currently have a table for "Contacts" that stores the members "Name", "Phone Number", and "Plan Code". I have another table "Messages" for phone messages which contains "Date", "Time", "Message Action", "Message", "Message Response". The last table "Call Log" is for phone calls which contains "Date", "Time", "Type", "Notes". Both the "Message" and "Call Log" tables will be pulling information from contacts. Date and Time for "Messages" will be entered manually while "Call Log" will be entered automatically when the record is created. If possible I would like to have both the "Call Log" and "Messages" be on the same Form Layout in a tab setup. *Screen shot to be included*  The idea is to be able to create a record and when I begin to enter the contacts name, if it already has been entered before that it will automatically pull up with the contacts information. For extreme added bonus, when I pull up the contact I would like a portal to reflect any messages or calls that relate to that contact which I can click on and it will be pull up that message or call log depending on which tab is currently active. 

      If anyone can please help me I would really appreciate it. I've made a couple attempts but im really stuck. If need be I can start from the beginning to make sure its done properly. Thank you for any help you can offer


        • 1. Re: Trying to create a Message and Phone log
          /files/1afd90924d/FM_Messages_log_Tables.png 818x792
          • 2. Re: Trying to create a Message and Phone log

            Unless a given message or phone call should be linked to more than one contact, there is no need for the Contacts_Call_Message table. You can link contacts to the other to tables by contact ID and simplify your design.

            Whether you make that change or not, you can put portals to the message and call log tables on your contacts layout and these two portals can list all phone calls and messages linked to that contact.

            • 3. Re: Trying to create a Message and Phone log

              It would be one contact to multiple message or calls. That would def help and make things easier.

              When I enter a members name though it creates a new contact each time, I want to be able to start entering a members name, have it complete the information and when I create a new record for a message or call log that a the record is only created for the message or call log not in contacts unless its a new contact.

              Is this also the correct relationship?

              Thank you again for your help! :)

              • 4. Re: Trying to create a Message and Phone log

                The relationship looks correct but your layout design is not correct if entering a name creates a new contact record. I see no reason to enter the contact's name at all except to perform a find for that contact record. The contact name should be a field defined on the contact table and placed on the contact layout. Since it's on the contact layout, your portals do not need to store or repeat that info each time you log a new phone call or message.

                • 5. Re: Trying to create a Message and Phone log


                  Just looked again at your relationships and they are NOT correct!

                  You should define them as:


                  Contacts::__pkContactID = Message_Log::_fkContactID
                  Contacts::__pkContactID = Call_Log::_fkContactID

                  Whether or not you use my naming conventions is up to you, but you must define __pkContactID as an auto-entered serial number and define _fkContactID in the two related tables as simple number fields (no auto-enter setting for them.)

                  • 6. Re: Trying to create a Message and Phone log

                    Okay thank you! Fixed that. As for the layout, what would the be the correct way to set up it up. The idea is when I get a call or message I would like to enter the contact information to start with, if its a contact already recorded for it to pull up the rest of the information. If it is a new contact then I would like to add it to the contacts. Once the contact information comes up I would then add the call or message details.

                    *Light Bulb* so the message portion or call log portion should be in a portal not just in the layout... is that correct?

                    • 7. Re: Trying to create a Message and Phone log

                      Okay I think i've almost got it... I added a portal and set up the fields within the portal. The problem is still the contacts. Im not sure how to set it up so that when I enter a name it searches for the contact and if not there allows me to enter a new. Its probably a simple and sorry for not seeing how to do it... I think I just need more coffee. 

                      Thank you again for all your help with this, could not have done it without your assistance!

                      • 8. Re: Trying to create a Message and Phone log

                        Add a global text field to your system. Set up a script such that when you enter a contact's name into the global field, the script performs a find for all contacts with that name. Or you might use the same method to search by phone number.

                        See this thread for examples of scripted finds: Scripted Find Examples

                        • 9. Re: Trying to create a Message and Phone log

                          A few Questions:

                          1) As for the layout. Since I want to have a tabbed layout in which one tab is for "Messages" and the other is for "Calls". Which table should the layout be placed? 

                          2) Want to verify I made the correction relations for my tables.

                          3) As for the script, I found one form another person you helped that was doing something similar to what i'm trying to do:

                          ***If [ IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "Contacts" ) ; Message_Log::gNameFind ) ) ]
                             #If name entered is not in contacts
                              Show Custom Dialog ["No contact found. Do you want to create a new contact?"] 
                              If [ Get ( LastMessageChoice ) = 1 ]
                                 Freeze Window
                                 Go to Layout [Contacts_Form (contacts)]
                                 New Record/Request
                                 Set Field [Contacts::Name ; Message_Log""gNameFind ]
                                 Set Variable [$Contacts_Id ; Value: Contacts::Contacts_id ]
                                 Go To Layout [original layout]
                                 Set Field [Message_Log::Contacts_fk ; $Contacts_id ]
                              End If
                          End IF***

                          This is the scrips after entering data from my information, but using the same format. When I type in a contacts name that exists it still asks me if I want to create a new contact.

                          Thank you for being patient with me and all my questions. I think i've almost got it. Thank you again for all your help!

                          • 10. Re: Trying to create a Message and Phone log

                            1) Contacts. Then put a portal to each of the other tables into your tabs.

                            2) I see no need for the added occurrences of contacts and I wouldn't put the global search field in either log table. I'd put it in Contacts or in it's own table reserved for global fields not used as relationship match fields.

                            3) I suggest sticking with the examples at the beginning of the thread of scripted finds that I recommended. You are searching out contact records right on your current, contacts based, layout. No need for Filtervalues as you are not using a check box field. Just use the simple find script:
                            Enter FInd Mode[]
                            Set field [Contacts::Name ; Contacts::gName ]
                            Set Error Capture [on]
                            Perform Find []

                            • 11. Re: Trying to create a Message and Phone log

                              Okay! So I got everything up and running! After playing with a few settings and a lot of trial and error I think I got it the way I want it. Thank you for all your help! If possible was wondering if you could take a look at it and see if I made an errors I may have overlooked... also their is one little thing that is happening and can't figure out why. When I enter data into the portal it creates and extra row with no information and I can't get rid of it. It's a bit frustrating. Thank you again for all your help and patience! :)


                              ...So I feel stupid asking... but how I do I upload the database? :/

                              • 12. Re: Trying to create a Message and Phone log

                                That "extra row" is standard expected behavior for a portal that is based on a relationship where "allow creation of records via this relationship" is enabled. That option causes that extra blank row to appear so that you can add new records to the portal's table by entering data into the fields of that blank portal row. If you do not want to use that method to add records to the portal's table, go to Manage | Database | Relationships, double click the relationship line and then clear the check box for this option.

                                • 13. Re: Trying to create a Message and Phone log

                                  Ok that  makes sense. It was just frustrating because I didn't know why it was happening.