on the job checkbox-ing

[queue09]

I've only been using FileMaker for a few days. They handed me a huge FileMaker 10 manual and i got through about chapter 4, before they handed me over this database to work on.

A large part of my trouble stems from the fact that I'm not sure how the database is supposed to be or work. The guy who was working on it before me I think made some changes he thought they wanted, but didn't finish those changes, so gave me a non-working database in a halfway finished state. If it were an already working database, at least I could see what buttons were doing and such, right now I have buttons that do nothing, and I'm not sure if they are supposed to have a script attached to them or what the plan was..

Okay so here goes.

A department has a database where they have a business, SomeBusiness and it works with some schools. So what they do is they enter in the Business name, mailing, contact info and then there is a large checkbox value list where they check schools that this business is working with.

Now I think they want a check box list because they can say "okay we have SomeBusiness and it is working with this school [check] and this other school [check]."

It seems like it would make more sense to have it be a radio button list and then have one record with SomeBusiness and one radio button with this school. And a second record with the SomeBusiness and a radio button with a second school.

But it doesn't look like that's how they were doing it.

So with the current checkbox value list, I thought that when they check on a school name in the layout, it would update that information in the business table with the check result. But that doesn't seem to be correct.

My problem is that, even though I check school name values for a record on a layout, it doesn't reflect that value change for the School_Name field in the business table. Obviously the layout is storing that data somewhere, because as I scroll from record to record, it has kept all my checked X's but in the table it doesn't reflect that. If I check several boxes I would like to see at least one value I checked for the School_Name field. But it's blank.

So I think it must not be set up right then. It seems like School_Name field in the table isn't linked to anything and the Checkbox set with checked school names in it, is also somewhere off by itself not linked to anything.

I want the values checked to be recorded in the business table as "School_Name". But I guess that's not what I'm doing with the checkbox value list. I'm recording a Checkbox Set object on a record in the layout, not in the table view, that has a list of values stored in it, and nothing is getting recorded to the table record.

If I make a change to the radio buttons on the layout, those show up changed on the table. What is it with the checkboxes that isn't working? :-/

Thanks for helping me out with this, I'm trying to do "on the job training" and so far it's been a mess. So any help you guys can give me is appreciated!! :

Edited October 19, 2010 by Guest

[comment]

It's a bit difficult to follow (not your fault, you just don't have the vocabulary yet). I think your value list should be defined to use values from the SchoolID field in the Schools table, also showing the school Name. Then use a text field in the Business table to store the IDs of the schools that the business is associated with (and only the IDs).

However, your instinct is correct: this is not a good approach overall. You have a many-to-many relationship here (one business - many schools; one school - many businesses). This is best handled by a third join table, where each business-school pair is a separate record. See a basic example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[queue09]

I see how a join table would make the whole thing a lot cleaner, but I'm still not sure how the checkbox set works. When you check off things, what are you doing? How is that stored in the database?

Theoretically, I can imagine a script that checks

if{ notEmpty( School_Checkbox_Set[1]))

set(tbl_Business.School_Name = School_Checkbox_Set[1])

}

But FileMaker Pro must have a system of figuring out how to store those values.

Record 1

id: [234]

_________

Box 1 [x]

Box 2 [ ]

Box 3 [ ]

Box 4 [x]

Box 5 [ ]

---------

Record 2

id: [235]

_________

Box 1 [ ]

Box 2 [x]

Box 3 [ ]

Box 4 [x]

Box 5 [x]

---------

It stores those things somehow?

Table tbl_Sample

field [id] field [boxes]

rec 1 234 1;4

rec 2 235 2;4;5

something like this I think?

Oh nevermind. It is working afterall. It's storing all those values in a drop down list under School_Name.

'Repetitions' is grayed out so they aren't showing up, but they are there. It's just showing the first School Name. And when I cleared out the check boxes it cleared it out in the table. So It's working fine then.

Sorry false alarm.

[comment]

In your example, Record 2 contains "2¶4" in the checkbox field (a return-separated list of checked values). You can see this if you place another instance of the same field on the layout and format it as edit box.

Beware of linking records by names (and other meaningful data). It's best to use meaningless serial numbers for this, so that if a school changes its name, the links do not break.

---

BTW, you might enjoy reading this:

http://fmforums.com/forum/showtopic.php?tid/193440/

[queue09]

So I'm trying really hard to work with this database with how it is set up, but I have to say, I hate it. The way it's set up is a mess. 1 Business record with 15 schools in a checkbox list that I can't even see unless I click on the field in the record and see the drop down list. >.< I realize on the layout it makes sense, but on the back end in the table it makes no sense at all. I'd like to be able to pull the school ID from the name and with that get the School's address, phone number etc. information, but right now I can't even do that because I have a whole series of data that isn't broken up. *rolleyes* Whoever designed this and set it up before me, I hate you.

So I'm thinking I may just end up breaking everything apart. Do what you suggested make a table with one business record one business ID. Keep my school table. One school one ID. Then make a third table with contacts with a flag for primary or secondary. And then use a fourth table that has one unique record with a business ID a contact ID and a school ID.

That way they pick a business from a list and a school from a list.

The business will know its own address and it will have a contact id field that matches to a contact record that has a name, phone number, type of contact primary or secondary. The school Id will link to one school record that has address, phone number, contact id that matches to a the same contact table with type of contact principal or assistant principal.

Which is probably how it should've theoretically been set up in the first place. Each table keeping track of its own unique data and one table that rules them all.

Now if I can just find somehow to export, import and keep all the data correct without losing any record information... -/

Best get to work I guess... :-/

[comment]

I am afraid you have lost me somewhere along the way. It's true that a join table is the best way to implement a many-to-many relationship.

But it's also true that it CAN be implemented without it, though some restrictions will apply. Most notably, there's no room for any attributes that are specific to a join, e.g. Business A works with School X since 1994.

ManyToManySans.zip