Telephone Analysis Help

[Mickirl]

Hi

I am switching from excel to fmp to carry out telephone analysis. I have two layouts, one is a call traffic layout and the other is a phone book layout. If I import a list of numbers in the the call traffic layout, i want fmp to check the phone book and assign a name on the call traffic layout to any matching number from the import list.

I used a vlookup function in excel to do this.

Further to that, if i import call traffic from a number of phones, i want fmp to identify the common numbers among the different lists. Is this possible using file maker.

Thanks very much for your help.

[Fenton]

"i want fmp to check the phone book and assign a name on the call traffic layout to any matching number from the import list."

FileMaker has a similar function, Lookup. It is available in the Options/Auto-enter for a field. It is also available as a calculation function, Lookup().

In a relational database, you don't really need to lookup the name, as it is always available via a relationship from the calls list to the phone book based on phone number. You need that relationship to use lookup anyway. But it's up to you.

"i want fmp to identify the common numbers among the different lists"

The phrase "different lists" is not very precise. There is only 1 list, 1 table of calls. You likely mean "from different imports."

FileMaker has a Find command, which is available via the user interface, or as a script step. There is a special operator, ! , which means, "find duplicates." Just go into Find mode, and type ! into the field, then hit the Enter key. This will Find in the entire table. You could enter a date range or whatever to restrict the Find.

If you then sort by phone number, you'll see blocks of common numbers.

Perhaps a more useful way to see them is to add a Subsummary Part, with the phone number as the "break" field. It will show only line for each phone number. If you have a "constant 1" field (calculation, = 1), and a Summary field counting the constant, it will tell you how many for each phone number (in the found set). You can then remove the Body part, and see only those Subsummary totals. This is called a "report."

PhonesNumbers.fp7.zip

Edited October 8, 2010 by Guest
added example file

[Mickirl]

Hi Fenton

Thanks very much for your reply. In my original post, I was a bit vague in explaining what I am trying to achieve.

I am using Filemaker as a tool to assist in the investigation of analyzing telephone records from people who are involved in Organized Crime .

Basically I would receive several call traffic records different telephone numbers for suspects involved in the commission of an offense. In the past, because there would be a large quantity of call traffic per telephone no, I would import all these call records into Excel and filter down the call traffic to calls of relevance. With excel I was able to create a phone book where on a seperate sheet, i would manually enter known telephone numbers of suspects and by creating a v-lookup function, excel would then automatically place a name beside a matching number on the call traffic for that number. This saved me alot of time because I did not have to manually go through every call and see if the called number was one of our know phone numbers.

The only thing excel was not able to do was to identify a common number, ie a number that may have been in contact with some or all of the suspects telephone numbers.

I am going to look in to the lookup function and hopefully I will be able to get that to work.

Again thanks very much for your help. If you have any further ideas, they would be most welcome.

Kind Regards

Mick

[Fenton]

This example file is an extension of the earlier ones. It includes the ability to see who called the known people. The "calls out" is also filtered, to only calls to known people.

On the Calls layout you can Find Known.

The Summary reports would then show who called who.

P.S. I also split the Relationship Graph into two table occurrence groups (TOG). So that Phones had a central location to Lookup the "caller" and the "called". It helps keep the graph from getting messy if you do more with either People or Calls.

Phones_InOut.fp7.zip

Edited October 10, 2010 by Guest