How would I do this?

[Heathbo]

I am building a call log database. This database has 3 tables. The Call Log is where all the details (date, time, reason, who) are stored. The Contact table is where all the names of the people who called are stored. Keyword is a list of certain keywords that a script uses to find out how many times a keyword comes up in the call log.

My question is, currently the script I wrote goes to the keyword table, sets a variable to the name of the keyword. Goes to the Contact table and sets another variable to the name of the contact. Then goes to the Call Log, enters find mode, then sets the contact name and keyword. Then performs find and reports the found count. This basically tells me how many times this contact has called about this subject.

While this may work, what I would rather have is a relationship that would automatically update the found count. The problem is, new keywords are always being added. So it would be a big pain to create a field in the Contacts Table for each keyword.

Any ideas.

[aholtzapfel]

Any ideas.

I have a few but I'm not sure what would work for you. Where are you searching for the keywords(what field)? do Reasons=Keywords? are the keywords embedded in notes or ...?

[Heathbo]

Keyword table has a field (titled keyword) with the keyword in it.

Every record in the call log is attached to a contact in the contact table. I wan't to show in the contacts table, how many times each keyword has shown up in the Call Log for every call made be that contact. Does this make more sense?

[comment]

This point is not clear: when users log a call, and they want to tag it with a leyword - do they need to select a keyword from an existing list, or can they just make up new keywords as they please?

[Heathbo]

Keywords are not tagged in the Call Log. The Keywords table is a stand alone table with a single record for each Keyword. The only thing that gets tagged in the Call Log is who called (from the Contacts Table). At this point I've been running a script that performs a find in the call log. Performing a find for each Keyword in the Keyword table.

ie.

1. Jump to Keyword Table

2. Set variable to name of keyword

3. go to Call Log

4. enter find mode

5. set the reason field to the variable

6. perform find

7. record get found count in results text field

8. Jump back to Keyword Table

9. Go to next record

It continues to loop like that and the script ends after the last record in the Keyword Table.

Of course the problem with this is its not live. I was just wondering if there was a way to do this with a relationship instead of a script. Again the problem I run into is that the number of keywords is always increasing.

[comment]

OK, now I am confused. What exactly is in the reason field, and how does it get there? Just to clarify, I am asking about the process that creates the call logs, not about your current problem of getting stats from them.

[Heathbo]

The reason field is in the Call Log talbe. It is filled in by whoever created the record. It is the reason for the call.

[comment]

I am afraid you are not answering my question. Is the reason limited to an existing keyword? Can there be more than one reason for a call, and if so, how is that handled? An example would be useful, I think.

[Heathbo]

The reason field is not limited to one or to any keyword. There is no relationship between the keyword table and call log table. The only thing the keyword table is used for is a place to store all the possible keywords. The way the database works is.

1. You get a call

2. create new record

3. Fill in who called (linked to Contact Table) in the contact section.

4. Type in the reason or the reasons of the call in the reason text field.

Then if I want to find out how many times a contact has called about a certain topic (ie keyword) I can enter find mode, type in the name of the contact in the contact section, and the keyword in the reason field. Then perform find. There is nothing else to this. I would rather have a relationship that does this for me rather than relying on a script.

[comment]

This is not simple at all. However, if you're satisfied with limiting this to one selected keyword at a time, it can be simplified this way:

1. Define a global text field gKeyword in the CallsLog table.

2. Define a calculation field cExists in the CallsLog table (result is Number) =

GetAsBoolean ( Position ( Reason ; gKeyword ; 1 ; 1 ) )

3. Define a calculation field in the Contacts table (result is Number) =

Sum ( CallsLog::cExists )

Put the gKeyword field on a layout of Contacts. Now, when you enter a keyword into gKeyword, you will get a count of calls that contain the selected keyword in the Reason field.

Note that since users are not forced to use exact keywords when entering the reason for a call, you might encounter some problems - but this is equally true for your current method using find.

[Ugo DI LUCA]

Michael,

I'm afraid this would not exactly solve the issue as from my understanding, what Heathbo is asking is a live count for each keyword.

As you pointed, since users are not forced to use exact keywords when registering a call, there cannot be a really reliable solution for this problem.

As it goes, I figure a keyword may be a single word, or a composition of several. If not, we could use an indexed key with each word in the reason field, and use a relation from the keyword table to the log table on that indexed calculation in that call log table.

With a direct consequence to the size of the file, though.

One other option would be to set a keywords indexed field in each Call log record, at the moment the call is registered. It would reverse the current process, that is that you'd investigate your keywords table based on the content of the reason field.

The keywords table instead of having a single field, would have both a index and a matching index field

index problem

matching index : problems, problem, problematic, etc

Edited October 16, 2007 by Guest

[comment]

what Heathbo is asking is a live count for each keyword.

That's what I thought too, until he said:

I can enter find mode, type in the name of the contact in the contact section, and the keyword in the reason field. Then perform find. There is nothing else to this.

One other option would be to set a keywords indexed field in each Call log record, at the moment the call is registered. It would reverse the current process, that is that you'd investigate your keywords table based on the content of the reason field.

That would be my inclination (if a multiple count is indeed required). The problem is that the list of keywords is often changed, so if you want an indexed result, you would have to recalculate every time that happens. Unless you'd want to use that method, what's it called... ah, the Ugo method.

In any case, it's not going to be very fast.

[Ugo DI LUCA]

LOL

A FM9 Server script running everyday would do the job for all previous calls

And while we're at it, as my colleague Fabrice Nordmann pointed out a few days ago, let's have fun recreating the Today function as well so that everything is solid as in the times of FileMaker 5 :

[comment]

A splendid idea!

Seriously, though: how many keywords can there be? And how many calls from the same client? I guess a custom function could handle the intersection between these two in a reasonable manner.

[Heathbo]

You would be surprised how many keywords there are. Whenever I think there are enough, someone always brings up another. You also couldn't believe how many tech calls we get from the same people. I am basically using this to figure out call patterns among our clients (ie. what do they call the most about). I have a script that scrubs each word in the reason field, checks to see if it already exist in the keywords table, if not it then checks a keyword exclusion table, if it doesn't exist there then a new record is created in the keyword table and the new keyword is marked so I can verify if I want to keep it or not.

Edited October 17, 2007 by Guest

[comment]

You would be surprised how many keywords there are.

LOL, I see you will go to any lengths to avoid giving out actual information...

I am basically using this to figure out call patterns among our clients (ie. what do they call the most about).

If you want that kind of statistics, I think you'd best create a join table between calls and keywords. That would have to be scripted, of course.

if it doesn't exist there then a new record is created in the keyword table

You can get a lot of synonyms that way. No wonder your list is long. Of course I have no idea what this about, but I would try and pre-define the categories and force the logger to assign a call to one or more of them.

[aholtzapfel]

To my understanding (please correct me if Im wrong) the keyword table acts as a valuelist. There is a "contact" table(name, #), and a "call" table(date/time of call, duration, reason, notes).

What I would do is seperate the "reason" into it's own table(at least 2 TO of it) one TO would be related to the call by the Contact and call record indexs(used to enter multiple reasons for each call/ one reason per record), one TO related to the Keyword table by Keyword(for reporting).

Once the reasons are in there own table reporting gets a whole lot easier.

I know this won't help right now but long term I would try to do somthing like this.

[Heathbo]

if it doesn't exist there then a new record is created in the keyword table

You can get a lot of synonyms that way. No wonder your list is long. Of course I have no idea what this about, but I would try and pre-define the categories and force the logger to assign a call to one or more of them.

[comment]

A join table between calls (not contacts) and keywords (or even just a table of individual keywords used in a call) is required, if you intend to analyze statistics.

It seems a bit strange to mark all words as keywords, then filter out the unwanted ones, rather than just pre-defining the keywords of interest - but as I said (repeatedly), I don't know enough about this to offer an opinion.

[Heathbo]

Actually the join between contact and keyword works well. Let me try to explain my reasoning for everything I've done.

1. The call Log is just a record of who called and a detailed explanation of why they called.

2. The call Log is linked to the Contacts Table. This link is used to show detailed information about the person that called.

3. Within every Call Log record is a call reason field where people can type in a detail acount of the conversation.

4. The Keyword table is nothing more than a table that keeps record of every keyword I want to report on. ie. a new record for every keyword. My reasoning for this will be explained later in this post.

5. The Exclude Keyword table is nothing more than a table of words I don't want to report on. Again, I will explain this in detail later.

6. I use a script to tie all of this together. Again, I will explain this in detail later.

The way the keyword table, the exclude keyword table, and the script functions is:

Step 1 is to find the Keywords.

1. The script will go to the call reason field in the first record in the found set.

2. The script will look at the first word.

3. It will then look to see if this word is found in the exclude keyword table.

4. If the word does exist, the script will then go back to the reason field in the Call Log and look at the next word.

5. If the word does not exist in the Exclude Keywords table, the script will then look to see if it exist in the Keywords table.

6. If the word does exist, the script will then go back to the reason field in the Call Log and look at the next word.

7. If the word does not exist in the Keywords Table, a new record is created and that word is put into a keyword title field.

A field that stamps all new records at record creation will allow me to quickly find the new records.

8. This script will continue to loop through every word in every record untill every record has been looked at.

NOTE: The script puts a Date Stamp on every record it looks at. When the script runs again, it will compare that Date Stamp with the record's modification date. That way the script will know in the future which records its allready looked at.

9. After the script has run, I can quickly look at the Keywords Talbe which is filtered through a portal to only show the new records.

10. If the script found a word I don't want to use as a keyword (such as: with, now, he, she, etc.) I can push a button in the portal which has a scipt attached that will copy that keyword record to the exclude keyword table and delete the record from the keyword table. I ran into many words that fit this description the first few times I ran the script. I found that no longer happened once the most common english words were in the exclude keyword table.

This whole process happens in seconds and is designed to only find the keywords. This also frees me up from pre defining keywords.

The next step is to find out how often the found keywords show up in the call log.

1. A simple script is used to look at the keyword name in the first record of the Keywords table.

2. The script will then go to the Call Log Table, enter find mode, insert the name of the keyword in the call reason field.

3. Perfom the find and record the found count.

4. The script loops, continually looking at the next record in the Keywords table, ENDING AFTER THE LAST RECORD.

Using this method I can look up how many times every keyword has shown up in the call logs. Simply by including the contact's name in the call log, I can also find out how many times keywords have shown up for each contact.

All the join between the contact and the keyword tables do is keep track of how many times keywords were found for each contact. The benefit of this is, a portal in the contacts table can sort the the keywords and the found count in the join.

I hope this clears up a lot. Again, my main reason for doing it this way is so the system can find the keywords for me.

[aholtzapfel]

Ok, I think I see what your doing. I still think the reasons should be in their own table. (I would avoid pulling keywords from notes for reporting, if possible)

I have thrown together a sample file. It is crude but it might give you an idea or two.

Allen

CallLog.zip

[Fenton]

It seems to me that the piece you are missing, is as Comment says, a join table between Contacts and Keywords, or at least a multi-line field of keywords in each contact record. You're current structure is fine for Finds, but it needs an "index" of the keywords associated with each contact record.

Then you can have a live dynamic count for any given keyword; since, as you say, you've already extracted ALL keywords from each contact when it is processed (otherwise you'd have to update the index).

It would have been fairly simple to build when you were extracting the Keywords from the Contact. It can still be done; there's several method possibilities. One would be a modified version of what you used to extra the keywords. Another would be a modified version of what you use to count the keywords in the Contacts.

[comment]

Well the first process - the one that defines the words of interests - sounds like it should work. I'm just wondering why it's necessary to mark the call records with another date stamp (this might fail if a record happens to be opened by another user). It seems the stamp is a property of the list, and it should be kept there, or perhaps in a settings table.

I didn't quite understand the second process. If you want to look at a contact's record and see how many times THIS CONTACT has called about each keyword, surely you have to do a find for each contact/keyword combination. This doesn't seem very efficient - but efficiency is a factor of how many contacts and how many keywords are there, and how often do you need this.

EDIT: Fenton posted while I was writing. The above should be understood as an addition to what he said, not as an alternative.

Edited October 22, 2007 by Guest

[Heathbo]

That's kind of what I mentioned in the last paragraph. I'm using a join file between the contacts table and the keywords table. I'm using the join as a way to keep track of the found count for each keyword as it's related to the contact. Currently though the join is only updated by another script that performs the find for each keyword with the contact's ID#. A portal in the contacts layout shows all the records in the join file related to that contact. This is great for sorting by name or found count. But what I'm wondering is, is there a way to turn this into a relationship that automatically updates itself? Or am I forever tethered to a script? What do you guys think?

[comment]

If you want a join table then yes, the process of creating the join records must be scripted. You cannot create records by calculations and relationships. Either user enters the data manually, or a script does it automatically. However, the script could probably be simpler than doing a find for each contact/keyword combination (if that's what you're doing currently).

But I am still not sure if you do need a join table for what you want to do. If, for example, you only need to look at one contact at a time and see how many calls mentioning each keyword were made by this contact, it can be done without a join table.

[Heathbo]

But I am still not sure if you do need a join table for what you want to do. If, for example, you only need to look at one contact at a time and see how many calls mentioning each keyword were made by this contact, it can be done without a join table.

The join table is being used as an index. How would I do this without a join table?

[comment]

I don't know what you mean by "being used as an index". Frankly, I am getting tired of having to ask you for more information every time.

[Heathbo]

I'm sorry, I thought I spelled everything out in english above. Now that you hopefully understand the work flow. How would you do it?

[comment]

I'm afraid I haven't got a clue. I believe all you said about this was:

I am basically using this to figure out call patterns among our clients (ie. what do they call the most about).

All the join between the contact and the keyword tables do is keep track of how many times keywords were found for each contact.

I'm using the join as a way to keep track of the found count for each keyword as it's related to the contact.

If there's more and I have missed it, why don't you point it out specifically?

Now, I said that if you only need to look at one contact at a time and see how many calls mentioning each keyword were made by this contact, it can be done without a join table.

I think the expected response is either "Yes, that's all I need to do" or "No, I also need to do this and this". I have also stated at least twice that the number of contacts and keywords is significant in selecting the best approach. I don't even know if you are doing this as a periodical report (and how often?), or if this is an aid in handling a call from a client - and therefore needs to be produced "live".

In short, after 29 posts you still haven't told us the most basic things.

[Heathbo]

Now, I said that if you only need to look at one contact at a time and see how many calls mentioning each keyword were made by this contact, it can be done without a join table.

The answer is "Yes, thats all I need to do"

I have also stated at least twice that the number of contacts and keywords is significant in selecting the best approach. I don't even know if you are doing this as a periodical report (and how often?), or if this is an aid in handling a call from a client - and therefore needs to be produced "live".

The number of contacts and keywords is not set in stone. It is always increasing. I imagine after time the number of keywords will level off. The number of contacts will always increase. This is an aid for handling a call from a client. So this needs to be produced "live"

Sorry about the miscommunication. The is obviously a good example of "in my head, what I said made sense".

[Vaughan]

"The number of contacts and keywords is not set in stone. It is always increasing."

I think this is a BIG mistake. The keyword list should be as short as possible. Adding to the keyword list should be a major process. Otherwise you'll end up with 100 keywords each with 1 support call: useless information.

[comment]

Take a look at the attached file. I think it shows about the most you can get without using a join table.

You could also add the field cCountCallsByContact to the Keywords portal on the Contacts layout - but this would require scripting all navigation in the Contacts table. The field will show the results for the contact selected in the global field Keywords::gContactID - which may not be the currently viewed contact. So every time you move to view another contact, you would need to 'sync' the global field in Keywords to the currently viewed contact.

CallLogStats.fp7.zip

[Heathbo]

Wow, I never thought about doing it that way. There are a bunch of fuctions in there that I'v never used. I'll have to hold on to your example for reference.

I now see that a global in the keywords table is a must to count how many times the keyword was used. Is it possible to: base the relationship on the ContactID in Contacts, instead of linking to gContactID in keywords? Or maybe a way to make (through calculation) the gContactID always equal the ContactID of the contact your viewing in the Contacts Table.

[comment]

I'm afraid the answer is no to both your questions. However, you could try using one of the event-trigger plugins to run a script upon a screen refresh in Contacts.

OFF TOPIC:

If Filemaker gave us a function to get value from the current record, it would open a whole new lot of possibilities.