One-to-many creation tutorial

[Ian Murphy]

I feel self-conscious asking this rudimentary question after I've been using the product for years . . . but I guess I've never needed to do this before. I'm sure there's a tutorial out there and would appreciate a pointer to it. Googling combinations of my question hasn't turned up the answer.

 

I've always done one-to-many relationships with fixed 'categories.' First, we create the types of things we're doing at the library (programs for kids, programs for adults), then we create the entries that fall under those types (Story time class and finger painting under programs for kids, computer class and Guest lecture under programs for adults). Same model for P.O.s we issue for purchases and for the phone directory tying local people to their businesses.

 

But for the first time, for an internal budget tracker, I'm looking to add categories on the fly.

 

So I intend to start with the categories yellow and red. When we buy lemons and bananas, staff will tie them to yellow, when I buy tomatoes and stop signs, I'll tie them to red. But when a staff member buys blueberries, and types category blue, I want FMP to create that category if it doesn't exist.

 

Every way I've created the relationship (between table category and table item), it ends up one-to-many backward - so I end up with several entries called 'red' and several called 'blue.' I don't think I want to relate them through a join table, but maybe I'm missing something.

[James Gill]

Is there a particular purpose to creating a 'Categories' table instead of, say, a user-modified value list that contains a list of approved Categories?

 

If so, then I think the best solution would be to make the 'Item::Category' field in the 'Item' layout be a relational-value list that looks up related values from the 'Category:Description' field.  It should auto-complete the related values as the user types them, and if you place either an OnObjectValidate or OnObjectExit script trigger onto the field, you should be able to check to see whether the value they've typed already exists in the related table and, if not, create the value on the fly.

 

Of course, you'll open a whole can of worms with spelling issues and users entering all sorts of values if you do this.  In my experience, allowing users to add values as they see fit to something as seemingly defined as 'Categories' is a bad idea...YRMV.

[Ian Murphy]

The reason for the categories to be a table: this will be an informal budget tool for departments within an institution. At the end of the year, the accountant will still total up, in QuickBooks, the actual money spent. But in the meantime, this is a tool for a department head, with two or three buyers under her, to track spending week in and week out. So if we end up with one category for 'office supply' and another for 'office supplies', it's acceptable, so long as all the spending is accounted for. The thing that can't happen is: we have budgets for yellow and red, and you buy something blue, and I don't notice that the money's been spent.