Hello,

Band new here and brand new to Fm and databases. In short... I know nothing, but I know that everything is possible. I am trying to set up a very detailed inventory and invoice DB for my little business.

I need to get my head around the some of the basics. In order to do this I need some help to understand a very simple invoice, line item and products DB and the relationships.

The tables and fields are as follows:

Products: ProductID, Type, Item, Price

Line Item: LineItemID, ProductID, InvoiceID, Type, Item, Price

Invoice: InvoiceID, Total

So far copying from a template I found I have set up an invoice with a portal. Using a list of ProductIDs I can chose the productID that interests me and using lookups referring to the ProductID the Type, Item and Price appears automatically. Great but not practical.

I want to first chose the type of product, when this is done chose the product that belongs to this type, the price should them appear.

It pains so much to ask this because I know it is straight forward.

All help is greatly appreciated.

p.s. I just tried to upload the simple DB I made to explain the problem but not allowed (think I understand why), I hope the question is clear enough.

I hope the question is clear enough.

Not quite. Perhaps this can help:

http://fmforums.com/forum/showpost.php?post/357335/

I just tried to upload the simple DB I made to explain the problem but not allowed (think I understand why)

Did you zip the file?

Thank you.. very helpful.

the file is now zipped..

I need a little time to absorb everything...

Thank you.

Invoice and Inventory.fp7.zip

In the example you attached.. is it necessary to have the attributeID in the portal? What if I simply wanted to chose a attribute by its name, not its ID?

I would chose the category (eg "shape") and then chose the attribute (e.g "circle").

Try the following: select File > Mange > Value Lists… and set the "AvailableAttributes" value list to "Show values only from second field". Next, enter Layout mode and format the AttributeID field in the portal as Pop-up menu.

Note:

1. For this to work, attribute names must be unique.

2. You are still selecting an AttributeID; this is a good thing - it allows you to rename an attribute without breaking existing links.

Thanks.. that definitely works. I am struggling to really understand the meaning of the table availableattributes and the relationship with objectattributes. I need to get my head around this somehow... any suggestions

I am struggling to really understand the meaning of the table availableattributes

It's not a table - it's an occurrence of the Attributes table. The relationship, based on matching Category, "filters" the attributes - only attributes in the selected category are related.

thank you. I finally managed to recreate the same object/atributes db you showed me and I think I understand roughly how it work.

I tried sending a pm to ask if you if there was a chance in a million that you would want to spend an hour showing me on teamviewer/skype how to set up some relationships in a simple database... or more importantly to understand how I should be thinking about it. i'll cross my fingers.