many to many and filtering set up question

[sean o mac]

I have one table called Tours, and another table called Personnel. It would be a many to many relationship as one Tour will have many Personnel, and any one Pesonnel record can be part of more than one tour. OK, so I get the many to many part and know I have to create Join tables. Onto the real question.

Each Personnel record is distinguished by category (Artist, Travel Agent, Technician, etc.) and associated to the Tour table in only that particular way. Each Personnel record is set up so its validated by a specific Category. The end user cannot create a record without choosing a Category, so that part is done.

What I need to do is build a distinct 'set' of associations for a particular Tour so I can know who is part of it, and also ultimately to print out these names and their associated information. As you might imagine, this needn't necessarily be a Portal as most of these are one name associations at the time of the build itself, though I am fine with using 1 record portals if its easier - as long at the end result prints properly.

So, how do I do this? Do I need to create as many table references from the Join table to the Personnel table as there are possible associations, all using the same fk_recordID::pf_recordID relationship yet naming them accordingly? If so, should I presume I only need to create a single relationship between the Tour table and the Join table or would I have to do the same number of table references on both sides of the Join table?

Finally, how can I filter (and thus display) only the relevant contacts in each part of the 'build' layout so as to not confuse the end user? Would I then have to create another nearly identical set of table references for the value lists, all filtered by Category? That seems like a LOT of table references, but if it works then its worth it.

Attached is a PDF version of what the end resulting layout should look like when this set of associations is built. Any suggestions on the relationship structure to accomplish this would be GREATLY appreciated.

thanks

personnelcontactsexample.pdf

Edited May 2, 2007 by Guest

[sean o mac]

I think I may have solved at least SOME of the question above by fooling around for a couple of hours. I think I just need to solve the filtered value list end of it so only the right records are seen in the appropriate places.

Please let me know if this attached file is the correct many to many structure for what I am trying to do and let me know if you can suggest how to create the filtered value lists I need to make this 100% functional.

thanks

TourPersonnel.fp7.zip

[comment]

Please do not double post.

[Søren Dyhr]

Michael have you noticed that all our effords has evaporated into thin air, the link is dead! ??

--sd

[Lee Smith]

Hi Soren,

I tried to merge the double Topics into one, and the Forum chocked in the middle of it.

I notified Stephen about this earlier today, and he is looking into the problem, to see if he can figure out what happen, and see if he can reverse it.

I don't have a lot of faith in the fact that we might be able to retrieve the other topic, so those of you who provided help in the lost topic, can replay it here.

TIA

Lee

[sean o mac]

I am sorry, but I am coming into this a little late and it seems I have upset someone when I didn't mean to. It also seems someone answered a post of mine and yet it has been removed from the forum. I didn't think that my posting was that much of a problem and I am not entirely clear how this solved anything.

I posted questions about value lists in one forum and questions about relationships in another. They are about the same set of tables, but if it seems I am posting twice its because I really don't know where the question is best asked. Because I am tackling two related (sic) but different elements of my problem, it first seemed best to post one way then I changed tack and tried it another way. I changed tack mostly because I am getting some concepts and teaching myself at least some of what I need to know along the way. How can that be a bad thing?

Please understand that its very hard to post the full file I am working on since it has thousands of records and a dozen tables, some of which I am slowly converging from separate files as it existed in FM6 to a single set of tables. I know this isn't the best way to work but its really the only way this solution CAN work as its being used everyday. To that end I appreciate any suggestions and help I have been offered so far to grasp something I wasn't getting before. I certainly am not here to upset anyone, just to try to learn from all of you, yet as you well know its very hard to even ask the questions sometimes so others can know how to answer them.

I sat down last night and came up with a simple file in an attempt to understand how I could do what I needed to do to see if I could post something that would articulate better what I was trying to do. Along the way I learned a few things. Its kind of working but I need to know if its completely off track before I dig deeper into it. At the same time, I can't get the value lists to work the way I need them to. Is this a relationship issue or is it a value list issue? Or is it both? I honestly don't know but if you can assist me that would be great.

thanks

[Søren Dyhr]

Comment and I did yesterday both make very similar templates (unfortunatly now erased ...nevermind it took only half an hour to make a new), and one thing we were agreeing on, was your idea of having the various types in their own tables, Didn't the 4 tables have a fair share of fields in common?? It's pretty inconvenient since some persons might maintain various functions, this would make your tables a nightmare to syncronize - the idea of having the same data in two tables is honestly not fully embraced as "referential integrity"

I did then include a collision catcher in the popups shown as well, and we debated back and forth if a person might do several jobs on a specific tour ...and here have my template perhaps a too strict assingment??

Since collision catcher works with the dates, of the tour - will a person be removed immidiately (dwindled) from the popup after being assigned, this means that the popup has nothing but the ID to show, hence the overlay of a webding shutter in the same colour as the fields background underneath, followed by a merge field bringing in the value from the persons table.

But all in all will the relevant popups show according the choise made in function, and the date range selection, so if a person is out touring with another act which collide with the present tourdates, won't he/she be shown in the popup!

I have put no measures to ensure, that reversed dates in the span, or a missing date ...luckily have the CF taken care of that:

http://www.briandunning.com/cf/231

So if you enter only one date will it be a one-nighter!

Enjoy

--sd

Tour.zip

[comment]

My attachment is still available here:

Link

It's just basic structure - there are no measures to prevent assigning the same person twice to the same tour (even in the same role), or against a conflict in schedule.

[Søren Dyhr]

Ah so mine might be there as well??

yep it is...

http://www.fmforums.com/attachments/uploads/1178181220-ToursMod.zip

I thought the directory were disclosed to the public ...anyways, it took only half an hour to make a new

--sd

[sean o mac]

Sorry I haven't posted in a while, but have been on 'tour' and working 20 hour days as a result.

As for your comment:

Didn't the 4 tables have a fair share of fields in common?? It's pretty inconvenient since some persons might maintain various functions, this would make your tables a nightmare to syncronize - the idea of having the same data in two tables is honestly not fully embraced as "referential integrity"

Whether it made sense or not, the whole reason I was doing what I was doing was to address exactly that - namely that ALL contact can be in one table (partly as you said because most of the information in each record is identical and also because its so much easier to perform a search for a specific person), yet I have to be able to place them into a 'tour directory' as per their specific job title. To that end, my structure only had 3 tables in it and I was tackling the issue as best as I could.

You concern that one person may be able to do more than one thing is valid, however this special case in my instance could only occur once in a while (a travel agent would never become a musician for instance). It seems you have solved it regardless so I needn't worry about 'special instances' - interestingly, the entire sysem is based on the start and end dates of each tour, so a lot of that structure is already built into the tables I have.

Ok, reviewing your example file, only a couple of questions at this time:

1 - can the checkbox format just as easily be changed to a value list or popdown menu since the list of categories may be too large for that kind of format? can you forsee any issues with this other than the fact that it might limit the enduser from choosing more than one job category?

2 - how do you get from what you have done to a layout like the directory type I sent in initially as a PDF document? I will still need to print this in that format for publication purposes.

Thanks for any insights - you are helping a ton.

cheers

Edited May 18, 2007 by Guest

[bl]

Hello Soren,

I have been studying the examples in this post and finding them very helpful. I have a similar scheduling problem which I have been struggling with that might fit into this type of model. I have a question about the function used in your "ExclusionList" calculation. I do not see how the X relation to a single personID in the relation maps to this calculation. When I downloaded the example for viewing, the function came up missing in the example...

My background with relational databases has been mainly confined to Oracle and PostgreSQL. I am having difficulty recreating relationships in FileMaker.

I seem to get into trouble when I try to create a summary report grouping data together under sub-summaries with portals in the Summary parts. I am not used to specifically defining the additional table objects in the relations and find them confusing. Can you offer any advice?

Thanks in advance,

BEL

[Søren Dyhr]

1 - can the checkbox format just as easily be changed to a value list or popdown menu since the list of categories may be too large for that kind of format? can you forsee any issues with this other than the fact that it might limit the enduser from choosing more than one job category

No just do it!

2 - how do you get from what you have done to a layout like the directory type I sent in initially as a PDF document? I will still need to print this in that format for publication purposes.

Can you spot the join table in the many2many structure, because it's where the reporting should be done, and whether or not you send it out as .pdf is your choise, the sortorder would the keyvalue linking to each function.

If you need me to show you how, let me know!

--sd

[Søren Dyhr]

the function came up missing in the example...

Yes it's a fm8.5 only function, where you need to substitute it with ValuelistItems( which unfortunately have it's problems with freshing. Another option is to utilize this CF:

http://www.briandunning.com/cf/542

Let me know if you need help, implementing it??

--sd

[bl]

OK, I have the function installed and I changed the implementation from this:

( Join 2::ForeignToPerson )

to this:

cf_ListR(Join 2::ForeignToPerson;

1;

Count(Join 2::ForeignToPerson):

I set the Result to Number and left the rest of the implementation as it was. Is this correct? How do I tell if it is working?

[Søren Dyhr]

Make a deliberate double booking and see if the dropdown is strained correctly, if you can assign the same person twice needs a little more tinkering to be applied! One error though is that the resulting field are bound to be text, otherwise will the pilcrows vanish.... with jiffy logic to be expected, say you have keyvalues

4

5

6

If you do the typecast into number will it pick (omit in this case) the ID

456

...if it exists.

--sd