Value list or separate table for "nested" related data

[davehebb]

Let me start by saying I'm truly a newbie, in all senses of the word. Don't let me fool you with my fancy talk, I KNOW NOT OF WHAT I SPEAK. That being said, here's what I'm trying to do...

In my contacts database file, I have a check box field called "shows" which is a value list of 8 different tradeshows that each client or contact attends. Each client may attend 0-8 of these shows, and the list may be adding new shows in the future. Here's where it gets complicated. Each show is done 1-5 times a year, and should also be listed by month - each show has it's own schedule, so there should be a related but separate field titled "month", which would be conditional. So, for example, if a client attends the "Shoes and Accessories" show, there might be an option to select 1-4 months, such as Jan., Apr., Jul. and Sep. That same client might also attend the "Women's Fashion" shows, which might be held only in Jan. and Sep. (different conditions.) I think I can figure it out so far, but here's where I'm lost - I need to be able to "attach" the date separately and specifically to each show, perhaps as a checkbox or dropdown list, but I need to be able to do a Find for all shows in a particular month. So, for example, if I have each show listed, with a "sub category" field of the dates, I need to be able to search all date fields across all shows! I need to find all clients that go to any show in Jan., for example. Also, this is just for one city. I need to add 4 more cities, each with a set of different shows, each show having their own set of specific months available and displayed next to each show name. I also need to be able to search across cities, finding if any clients attend shows in more than one city on the same month, and all sorts of other specific searches. I'm so over my head here, it makes my brain hurt.... I've been using the VTC tutorials, but there is nothing like that in there, or if there is, I fail to see how to make it work.

Here's kind of what I envision it to look like, with ( ) to show and empty check box, and (x) to show a filled one (just for one city - I can create a tabbed layout to include the other cities)

Client #1:

(x) Show A ( ) Jan.

(x) Mar.

( ) Jun.

(x) Show B (x) Feb.

(x) Jun.

(x) Aug.

( ) Oct.

( ) Show C ( ) Jan.

( ) Aug.

( ) Dec.

Client #2:

(x) Show A ( ) Jan.

(x) Mar.

(X) Jun.

( ) Show B ( ) Feb.

( ) Jun.

( ) Aug.

( ) Oct.

(x) Show C (x) Jan.

(x) Aug.

( ) Dec.

etc. etc. - you can see how ugly it will look, not to mention the complications of having each have it's own set of months that refer to the same table of months... or maybe it makes sense to you, I'm just lost....

I had this idea that this kind of "nested data" would be a common task, but I have no idea how to approach it, nor do I even know how it should be displayed... As I've stated before, I'm seriously new and in over my head - act like you're talking to a 10 year old child...

And here's a test file that I did, and as you can see, I'm stuck...

showtest.fp7

I'm not really confident with scripting and calculations, but I might be able to muddle my way through it if I understood the big picture of how things are related.... Anybody have a clue?

Edited January 14, 2009 by Guest

[Dave Graham]

Forget the nesting, it just complicates things. In fact, you should think about the data before you consider the interface.

Here are your entities:

CONTACT. A person.

SHOWTYPE. E.g., Shoes & Accessories

SHOW. This is an instance of an Show Type on a particular date.

ATTENDANCE. A record that a contact has attended a show/event.

From the Contact record you would add a portal based on a relationship to the Attendance table. You would have two value lists in the Attendance portal: Show Type, and Show. When you select a Show Type, the value list in Show could adjust to only show future Show dates. You could then track attendance-related criteria in the Attendance record such as whether they have RSVP'd, attended, no-showed, etc.

Now that everything is properly related you can do reports based on the Contact's perspective, the Show's perspective, or the Show Type's perspective.

Note: I wouldn't use Month for your Shows; use Show Date instead. Should something change and you decide to offer to shows in the same month you don't want your solution to break. You can always do searches for particular months, so you don't have anything to lose with the actual dates.