Neophyte Question: Portal for order line item entry

[cjlesh]

Hey all:

I'm new to filemaker and relational databases. I am trying to create an application for handling orders of gift cards. My five tables (so far):/

Customers (customerId, customerFirstName, customerLastName, customerAddress1, customerAddress2, customerCity, customerState, customerZipCode, customerPhone, customerEmailAddress)

Vendors (vendorId, vendorName, vendorAddress1, vendorAddress2, vendorCity, vendorState, vendorZipCode, vendorPhone, vendorEmailAddress)

Cards (cardId, vendorId <-FK, amount, discount)

Orders (orderId, date, customerId <-FK)

OrderLineItems (orderLineItemId, orderId <-FK, cardId <-FK)

OK, good enough. My problem is constructing a layout for order entry.

I would like to be able to pick the customer name from a list instead of trying to remember /lookup the customer number, as there will be a few hundred customers. So I created a calculation field in the customer table called "customerWholeName" which is "last name, first name", then I use this field as a value list for the customerId field in the order entry layout. I'm not sure if I should use a popup menu, or a drop down list, or some other form a data entry that I don't know about.

I also created a portal to orderLineItems at the bottom of the order form, so I can add line items to an order. In this region I am having the same problem, how to add items in a user friendly way, so that people don't have to know the cardId, but can pick from a list of vendors and amounts (is this a conditional value list?).

Anyway, I am stuck at the conceptual level. How do people handle this common problem, making user friendly layouts to enter data when the actual field is a meaningless foriegn key?

Thanks in advance,

CJL

[aldipalo]

Have you looked at the "Business Productivity Solution" that comes free with FM 8? You can see how they handle the order processing , invoicing and contact management solution and get some good ideas from there. You can also use it as your template.

HTH

[cjlesh]

Thanks for the pointer. I checked it out, and there is quite a bit I can learn from that database, for sure.

But the invoice order entry stinks.

I have gift cards, which have a vendor (ex. Target, Walmart, etc...) and an amount (ex. $5, $20, etc...)

On the order form I would like two pop-up lists or pull down menus, whatever, the first letting me pick the vendor, the second the amount (the amount cannot be anything, it must be from the card table amount field, conditional to the vendor). Of course, the result will actually be the FK cardId.

I guess this is where I am stuck.

Thanks again,

CJL