Relating Customers to Advertisements & Select from Value List or else Create New Record

[kmninbox]

Hello, can someone give me advice on how to set this up, I don't even know if I have the tables and relationships correctly thought out:

I have a CustomersTable with the field "SourcedFrom" where I want to keep track of how a customer may have come to know about my business, this could be from magazine ads, flyers, tv commercials, word of mouth, etc. Eventually I want to develop reports on how many customers a certain advertisement or marketing venue is generating, and then even further, how many of those customers eventually actually make an order and how much…

But first things first,

In another table, I have an Ads_n_MarketingTable that the records will have a UniqueItemID field and have another field something like ItemName where the contents would be something such as Time Magazine March 2011, or Television Commercial #1, or even word of mouth "Jane Doe recommended," etc, basically it could be anything….I will be adding new records to this table directly when I run a new advertisement, or such.

Basically, ideally, let's say, 1 advertisement can have many customers related to it, but 1 customer should only be related to one advertisement venue….but what happens when there are multiple sources from which they've heard of my business?? arch…

Anyway, let's pretend I'm heading in the right direction…. In the CustomersTable and SourcedFrom field I want to have a drop down list of the ItemName field in the Ads_n_MarketingTable. Once I select a value from the drop down menu, I suppose it should make a new record where the customer id & name would be related to that value's id and item name, for example, once I select Advertisement#1 from the SourcedFrom field for Customer Jane Doe in the CustomersTable, the Jane Doe & her CustomerID would be related to the record Advertisement#1 in the Ads_n_MarketingTable.

Furthermore, I want it so that during data entry while in the CustomersTable, if the value is not listed in the drop down list, that you would be able to type in a new value, which would in turn add a new record to the Ads_n_MarketingTable, and be associated with the Customer that it was inputed for.

Is this making any sense? Will it be something like having an orders table and then a line items table for the orders? I have fiddled around with different match fields, from my CustomersTable & Ads_n_MarketingTable but can't seem to get it to work out. Basically if I have a drop down value list from the ItemName field in the Ads_n_MarketingTable, but can't make it relate to the customerName & customerID, or create a new record on that table if I type in a new value.

TIA for any guidance

[comment]

Anyway, let's pretend I'm heading in the right direction….

Let's not. The question whether a customer can have more than one advertisement venue is a cardinal one (if you'll pardon the pun). The solution will be significantly different if the answer is yes (three tables instead of two).

I want to have a drop down list of the ItemName field

No, you should be selecting and entering the ItemID value. You can display the name, but it is the ID that needs to entered.

Once I select a value from the drop down menu, I suppose it should make a new record

No, it won't. All it will do is assign the (existing) customer to the selected (existing) item. Creating a new item requires a separate procedure.

[kmninbox]

Tough love, I like it. Ok, ideally really, a customer can choose more than one source of advertisement... So then I would have to have CustomersTable, and AdvertisementTable, and a line items for the Advertisements table? is that correct?

What about the fact that if the data inputer doesn't find the advertisement venue in the value list, and I want them to type in a new value, and have that value automatically become a new record in the AdvertismentsTable? is that possible? this is my main problem at the moment, and it should apply wether the source is one advertisement, or many advertisements if I'm not mistaken.

[Vaughan]

n another table, I have an Ads_n_MarketingTable that the records will have a UniqueItemID field and have another field something like ItemName where the contents would be something such as Time Magazine March 2011, or Television Commercial #1, or even word of mouth "Jane Doe recommended," etc, basically it could be anything...

You'll get better statistics if you limit the responses that people can enter. Otherwise every response will be different and your report will simply be a list of 1,000s of responses with a count of "1" next to each of them.

You probably really want to know how successful the print media advertising is, or the radio, or the television. Maybe one for word of mouth. Then another field could specify the exact magazine or radio station the ad was heard on.

It's really easy to spend effort collecting data that is useless for generating meaningful statistics.

[comment]

LOL, if you posed your questions other than propositions, I wouldn't have to say "no" all the time... for example:

this is my main problem at the moment, and it should apply wether the source is one advertisement, or many advertisements if I'm not mistaken.

No, you are not mistaken (spelling excepted).

Ok, ideally really, a customer can choose more than one source of advertisement... So then I would have to have CustomersTable, and AdvertisementTable, and a line items for the Advertisements table? is that correct?

If by "line items" you mean a join table between Advertisements and Customers, then yes.

What about the fact that if the data inputer doesn't find the advertisement venue in the value list, and I want them to type in a new value, and have that value automatically become a new record in the AdvertismentsTable?

They would either have to select "Other…" from the value list, and enter the specifics into another field - or pause and create a new record in the Advertisements table and enter the missing value there.

Many applications (esp. in Mac OS X) use an interface where one selects a value from a side bar - and there is "+" button at the bottom of the bar. You could present your data entry person with the same type of interface - this has no bearing on the data structure underneath.

As Vaughan duly noted, the structure really depends on what kind of information you're hoping to extract from the collected data. For example, you could have a structure of:

Media -< Promotions -< Exposures >- Customers

to measure the effectiveness of both different marketing channels and of specific campaigns.

[kmninbox]

I see what you mean about what I'm saying...?

Ok, let's continue...

yes I was able to figure out how to match the advertisementID and have a drop down containing the itemname as well. But what is the reason when I make the value list based on the adID "allow editing of value list" and "auto-complete using value list" are grayed out?

wish there was a way to do it using just one field with drop down or else add new record, trying to keep my form minimalist and cut down on user mistakes during input.

[comment]

The "allow editing of value list" is relevant only when to the value list is defined to use custom values. Adding a new value to a value list based on a field requires, by definition, entering the value into the source field in one of the records.

The "auto-complete using value list" will not work with a value list defined to 'also show values from..." (I think - the help is not quite clear on this point).

wish there was a way to do it using just one field with drop down or else add new record, trying to keep my form minimalist and cut down on user mistakes during input.

I believe it could be set up that way, using script triggering. But then your relationship would have to match on the name rather then on the ID - which means the name cannot be easily modified later. Also, if someone enters "Time" instead of selecting "Time magazine", a new record will be created and the two ecords will be summarized separately.

That's in addition to what Vaughan said about letting users enter whatever comes to their mind. This is precisely what a value list is designed to prevent.

Edited March 31, 2011 by comment

[kmninbox]

Yes if it could, I wouldn't mind giving it a go... actually it would be better for me if the user were not given the chance to edit existing records, & I've been able to prevent that using privilege sets. The thing with allow edit of custom value lists is that the data is not flexible enough nor the process stupid proof. The user might not know how to edit the list, or to add new value under existing values, the user might even delete existing values...

But as I have it now with selecting the ID, and then showing the value in another field, I have to have 2 fields, the user might get confused as to where to select and then how and where to add a new value, I'm talking about really really stupid users here, basically assuming user doesn't know heads from tails about computers, which is inevitable.

I just want to find a way to make it rock solid and fool proof, give the inputter less chances to be stupid. I wouldn't mind so much going over the DB myself once in while about records such as Time & Time Mag and making those corrections, etc. nor am I afraid of having toooo many individual records at the moment.

I appreciate the conversation thank you

[kmninbox]

So I suppose my next question, which is really is just my original question but more clearly put, is, having 2 tables one for Ads and one for Customers, if I want to work only in one field to assign an Ad source to a Customer, and I use the AdName as the match field instead of AdID, how to have the drop down list and be able to select to assign existing Ad (without the error pop up saying doing so would be creating a duplicate record of the same name), and how to create a new record in the AdTable if what I type in is not already on the value list? How would I approach this script and trigger?

[comment]

Have a look at the attached demo. I still don't think this is a good solution, on several counts.

Properly, the Category field in Items should be validated as member of the Categories value list, and the script triggered OnObjectValidate. However, suspending validation until a new category record is created is not possible, because records must be committed in order for the validating condition becoming true.

CategorySelectOrAdd.zip

[kmninbox]

Thank you for the demo, I will see if I can make sense of it and wether or not I can apply it to my needs.

[kmninbox]

Yes, consider yourself my hero. You can say no to me any time. This will work for me well, even if maybe only until I find out my business logic is the real problem. thank you much. xoxo