Relationship Items List with prices

[palba]

HI All,

I'm trying to build a very simple complex database. My goal is:

-Making a list of items, taken from another database, with their prices added automatically.

Each Item has a price in the "items database".

In the other I have an empty list that I'd like to fill with the data in the other file, let call it "calc_menu"

So I linked the 2 files, only with theis common "item" field.

I've created 10 field (es item 1 to 10) in the "calc_menu". With a Drop menu you can choose each item in the "Items_DB" file on every line.

Belong the fiels "Item" i've put "Prices", that should take their value from the Item_DB

The relationship works only on the first line and copies the new value in the other 9.

Item 1 I Price 1

Item 2 I Price 1

....

Item 10 I Price 1

Nothing happen choosing a different item in another line.

I've tried the same thing with the repeating function but doesn't works either.

Where I'm doing wrong? What I can do? Maybe I have to relate even the price field too?

I really hope to have been clear!

Many Many thanks ina dvance

Best Regards

Paul

[Keith LaMarre]

What you're trying to achieve doesn't sound hard -but it is hard to understand what is wrong - is there a way to upload a sample or picture?

HI All,

I'm trying to build a very simple complex database. My goal is:

-Making a list of items, taken from another database, with their prices added automatically.

Each Item has a price in the "items database".

In the other I have an empty list that I'd like to fill with the data in the other file, let call it "calc_menu"

So I linked the 2 files, only with theis common "item" field.

I've created 10 field (es item 1 to 10) in the "calc_menu". With a Drop menu you can choose each item in the "Items_DB" file on every line.

Belong the fiels "Item" i've put "Prices", that should take their value from the Item_DB

The relationship works only on the first line and copies the new value in the other 9.

Item 1 I Price 1

Item 2 I Price 1

....

Item 10 I Price 1

Nothing happen choosing a different item in another line.

I've tried the same thing with the repeating function but doesn't works either.

Where I'm doing wrong? What I can do? Maybe I have to relate even the price field too?

I really hope to have been clear!

Many Many thanks ina dvance

Best Regards

Paul

[comment]

I've created 10 field (es item 1 to 10) in the "calc_menu".

No, that's not a good way. You should use a third table for the selected items. What you're describing is basically an invoicing solution - see:

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

[palba]

No, that's not a good way. You should use a third table for the selected items. What you're describing is basically an invoicing solution - see:

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

Hi Thanks a lot . Yes it's almost what I'm doing.

In matter of fact, I need to choose the items, make calculations and the printout an invoice,

that i save in another db, with a script.

I can't really understand why i need the third table?

The method you suggest can be used in a much complex graphical interface?

Could I use a script, adding an Id to all the items, that copy the value in the field?

Many thanks for your time!

Regards

Paul

[comment]

You need a third table because the relationship between Invoices and Products is many-to-many (an invoice can have many products, and each product can appear on many invoices). Without a join table of LineItems, there is no room to enter data that is specific to a particular invoice/product combination, such as quantity.

There are other reasons as well: for example, if you want to summarize your sales by product, you need each individual sale (i.e. invoice line-item) to be in its own record - otherwise they cannot be sorted.

I'm afraid I didn't understand your other questions.

[palba]

You need a third table because the relationship between Invoices and Products is many-to-many (an invoice can have many products, and each product can appear on many invoices). Without a join table of LineItems, there is no room to enter data that is specific to a particular invoice/product combination, such as quantity.

There are other reasons as well: for example, if you want to summarize your sales by product, you need each individual sale (i.e. invoice line-item) to be in its own record - otherwise they cannot be sorted.

I'm afraid I didn't understand your other questions.

HI,

Many thanks for your help,

however, this procedure is working, but useful with few items.

In my case I have many items (>50) and it's impossible to work giving the quantity in the main list.

I need something simplier.

One interface. I choose 1/2/3 item, I give the quantity, the discount (main or single) and FMP makes all the final calculation (Total+Taxes), storing all in one record.

Really, I don't need to sort the items. The main window has to be the master window that read the external data.

I'm figuring out to make a script to do this, but actually I don't wonder how.

Many thanks

Regards

Paul

[comment]

this procedure is working, but useful with few items.

This "procedure" can work with as many - or as few - items as you want.

The main window has to be the master window that read the external data.

Have you looked at the demo file I have linked to? It uses a portal to enter the items. If you like, you can make the portal 50 rows tall - the equivalent of having 50 fields on the layout. Or, you can use a scrolling portal, giving you access to unlimited number of items.

I need something simplier.

One interface. I choose 1/2/3 item, I give the quantity, the discount (main or single) and FMP makes all the final calculation (Total+Taxes), storing all in one record.

I have already explained that this is not a good method. And it is NOT simpler. For 50 items, you would need 50 fields to choose the item, 50 fields to enter the quantity, 50 fields to look up the price, 50 fields to calculate the extended price ... and - most importantly - 50 relationships between Invoices and Products.