I'm not sure if this is the best forum for this question, so apologies in advance....

A quick summary of the problem: once there were only retail customers - now there are wholesale customers, too.

A client of mine bought FMP8 and copied a sample tutorial database for managing Orders, Inventory, and Customers. She soon realized she wasn't up to tweaking it to meet her needs so she hired me. I added a line-item portal to her Order layout, defined value lists of customers and inventory and attached them to pop-up menus, and so forth.

She navigates through the layouts simply by selecting them from the Status area - there's not a script to be seen anywhere. She searches for records by typing CMD-F and using her data entry layout as a find criteria layout. It works well enough for her.

Now she wants to distinguish between Retail and Wholesale Orders and Customers. The difference between a retail customer and a wholesale one is pretty slight, as is the difference between retail and wholesale orders. I figured that the simplest approach would be to add a "Type" field to Customers and to Orders, and create new layouts for Wholesale Customer and WHolesale Order.

It seems to be impossible to do this without some scripting. I now have separate Table Occurrences for Wholesale vs. Retail customers, which are self joins to Customers with uc_kRetail = Type and uc_kWholesale = Type, respectively. Similar thing for Wholesale vs Retail Orders. But to achieve these subsets, I either have to use GTRR or run a Find to get a found set. And of course, I can't let her use her old faithful Find command to find records, or she could easily reveal retail customers in the wholesale customers layout, or vice versa.

So what is the best way to achieve this Retail vs. Wholesale dichotomy? Is using one underlying table plus a record type indicator a good way? How best to maintain the illusion of two separate, logical tables in one physical table?

Thanks for any and all comments!

Chap

Hmmm one method may be using some kind of record level access with privilege sets. Perhaps you can have two different priv sets; one for wholesale and the other for retail. You can use a custom menu or just one script to change between the two priv sets.

Wow - that's an interesting idea.

I guess I was hoping for a slightly more, um, relational kind of solution. One thought I had was to generalize the Customer record by removing all type-specific data; create two new tables, Retailers and Wholesalers, to contain data specific to Retailers or Wholesalers; and establish a one-to-one relationship between a Customer record and either a Retailer or a Wholesaler. Inheritance, kind of.

As we say in Nashville, "does that make sense?"

Well it would be determined on what exactly are in the two tables. Not knowing your fields but guessing at it, I would probably say that from a normlization perspective, leaving it in one table is the correct way. I dont see it being that big of a deal to script the finds. or having her put in one extra criteria of "type".