Determining number of unique IDs within criteria

[ericd]

Hello,

I'm trying to develop a series of calculations that will provide an in-depth analysis of data captured from some marketing activities. I've got it working well except for one piece.

I have two databases - one contains source data from the site and one contains the parameters for analysis and the calculations that produce the report.

For example, the source data fields are:

User ID

Link ID

Message ID

Every individual that interacts with the site has a unique user ID, but they may generate records with many different Message IDs.

In the file that analyzes the data, I have calculations that count the number of times different values appear across all records. For example, how many records contain Message ID = 3.

Where I get stuck is adding an additional layer of complexity to the calculations. I need to count the number of unique Customer IDs where the Message ID = 3 (or 4 or 5...and so on). In spoken terms, how many unique customers are there with a Message ID of 3? I have tried using a subsummary - that doesn't work because it just tells me the number of records per User ID that exist, not the total number of individual User ID values across the entire database.

I also tried creating a Value List from the values in the User ID field, extracting the list of values to another field, and then counting the number of lines...but the value list doesn't update automatically on its own all the time. So that doesn't solve my problem.

Any ideas?

Thanks,

Eric

[The Bridge]

The key to solving this is what could be called a concatenated text field, e.g. User_Message_ID=User_ID & Message_ID.

This field would form the foreign key of a relationship, either a self-join with the source data file or from your report file. If a self-join, then just match User_Message_ID to itself. If from your report file, you can use a global text field which would be set by a script. Then, using the Count() function, you could determine the number of matching records, i.e. Messages from a given User.

Hope this gives you a good start!

[ December 12, 2001: Message edited by: The Bridge ]

[tlsparker]

With the following answer, I am assuming the Link_ID field

indicates the number of times a particular user links to a particular message. If that is the case, the first time a user links to a message, the value of Link_ID would be 1. If I am interpreting that incorrectly, ignore the rest of this message. Otherwise:

Expanding upon Bridge's suggestion, create a field:

User_Message_ID_Constant=User_ID & Message_ID&"1"

create another field:

User_MessageID_Link=User_ID & Message_ID & Link_ID

Base a self-relationship on a match between these two fields. Then, if you use your count function, you should only be counting the first instance when a user accesses a particular message. Assuming, of course, that I understand the purpose of Link_ID.

Tom

[email protected]

www.tgparker.com/filemaker

[The Bridge]

Ah! Now I'm reading you loud and clear, as it were.

Unfortunately, I've never had to do what you ask before, so this is off the top of my head. I'm sure others here will have a more elegant/tried-and-true solution.

My approach (again, off the top of my pointy head) would be to create a relationship using Message_ID as the foreign key. For the purposes of this example let's say that the relationship is from a global field, say, Global_Message_ID to Message_ID.

Then create a valuelist, e.g. vl_Unique_Users in your source data file based on this relationship. The values in the valuelist should be drawn from the User_ID field.

Define a calculation field: Unique_User_Message_Count = WordCount( ValueListItems( Status(CurrentFileName), "vl_Unique_Users"))

I just tried this out, and it seems that Unique_User_Message_Count will only update when you click into a field, so if you do go with this solution, you would have to involve a script to Go To Field[] or some such thing.

Sorry I can't be of more help than this.

[ericd]

Hi there,

Thanks for the reply!

Following your logic, I think that solves the first half of the challenge but still leaves us with the second half.

If I understand your solution, the following record set would generate a result of 3 when querying for the number of interactions when Message_ID = 1(Field order: User_ID, Message_ID, Link_ID):

1,1,1

1,1,2

2,1,1

1,2,1

What I'm trying for is a solution that returns a result of 2, since two users interacted with message 1, regardless of the number of times the interactions occurred.

MS Access has a feature in which you can specify that the result of an SQL query contain DISTINCT records only...that's similar to the funtionality I'm looking to replicate.

Thoughts?

Thanks again,

Eric

[ericd]

Hi Tom,

I'll explain my application in a little more detail...I'm managing an email program in which we generate messages to my company's customer base - in each email are links that lead to offers and various web pages. The URLs in each message are encoded with several variables, including the customers's unique ID, the number of the message, and the number of the link in the message.

When the user clicks, all this info is captured for analysis of the marketing program.

So Link_ID works a little differently in my usage than how you were thinking about it...but I will be able to use your solution for something else! Thanks!

[ericd]

The Bridge,

Thanks for your suggestion - I had come up with something similar but I think your solution is more elegant, so I'm going to try it.

I had run into the problem of getting it to update the value list automatically as well...I wish there were a way to automate it...like setting up a script to run when the record is displayed, but I'm not sure if that's possible.

-Eric

[The Bridge]

Thanks, Eric -- I'm glad I could help.

Re: scripts

When I first started using FileMaker I tried to avoid scripting at all costs. For example, I would switch layouts with buttons that would just Go to Layout and nothing more.

Well, I've since learned just how valuable -- essential -- scripts are to building robust FileMaker solutions. I'm certainly not as proficient as others on FMForums seem to be, but I think I'm getting there.

Anyway, my point is that you should look at building your solutions so that, for example, a script takes you to a data entry layout and either pauses until Enter (or a Resume Script button is pressed) or stops and a button on the data entry layout performs another script that processes the information. This approach gives you, the developer, natural control over the whole user interface/data entry process/whatever you want to call it.

With few exceptions, every button in my solutions calls a script.

Hm... think I'm gonna have to change my display name to The Pedant.