Find related records

[rainwalker]

Good evening,

I would be very grateful, if someone could help me.

I have two related tables, Shops and Products. Shops contains informations about the shops (address, contacts, etc.) and Products contains the name of the products, and their prices. Both table has an ID field - ShopId and ProductID-, and Products has a ShopId field, which is the foreign key. I would use a Report layout showing the prices of the MacBook and MacBook Pro in each shop, but the second product with the same ShopId is not in the list. Is it possible to display them in the same row of the Report?

I am a PHP/MySQL developer, and I am completely lost. I've tried to create a new related table to Shops, and then make a script like:

If[(Shops::ShopID = Products::ShopId) and (Products::ItemName = "MacBook")]

Set Field [LapTopPrices::MacBookPrice; Products::ItemPrice]

End If

If[(Shops::ShopID = Products::ShopId) and (Products::ItemName = "MacBook Pro")]

Set Field [LapTopPrices::MacBookProPrice; Products::ItemPrice]

End If

It does not work, of course (missing a "WHERE" option). I know, there must be a solution, but I can not find it...

[LaRetta]

Hi rainwalker, welcome to FM Forums!

What is LaptopPrices and how is IT related?

Basically, since one Shop can have many Products, you can use a field called ProductID. Create a value list of Products - all products based upon field values and select the ProductID. Also display the Product Name as second field and sort by it as well.

But until I understand what table occurrence LaptopPrices represents, I can't help further.

UPDATE: It also sounds like you're missing a piece of a puzzle here. Please explain what you're doing. You may want a conditional value list so that, if you order from a shop, only that Shop's products appear in the list for selection. When you select a product (by inserting the ProductID), then the price can be looked up through the relationship. But I need more information here - even picture of your graph would help ...

LaRetta

Edited December 31, 2006 by Guest
Added Update

[rainwalker]

Thank you for your answer.

The LaptopPrices table has a ShopId, a MacBookPrice , and the MacBookProPrice, showing those prices in the related shop. It duplicates the data, because those prices are already in the Products table, but I could not found out how to handle the find result, when the result is an array.

[rainwalker]

I have a table, containing the shops data, and another table, containing the products, they sell. If someone ask me: "Could you show me the Apple laptop prices at the resellers?", I would use list, with the name of the reseller, the price of the MacBook, and the price of the MacBook Pro (maybe sorted by one of the prices ascending, but it is not important now.) But these items are two records in the Products table, with the same ShopId, and only the first of them appears on the list.

[LaRetta]

I think we're mixing two issues: Wanting a report to display a list of associated products and entering a price into LapTopPrices. Can you post your file? Create a reply and, at the bottom, is Manage Files. Attach as zip or rar.

Edited December 31, 2006 by Guest
Added Update

[rainwalker]

Thank you or your kindness. I'm feeling completely idiot. -)))

[LaRetta]

Hey - it's tough being on a new board. And it's tough diving into something new (from another software perspective). It is NOT a reflection of intelligence at all. If you can't figure how to attach a file (or even a copy of your graph) then private message me here (go to my profile) and select 'Private Topic.' I'll provide my email in which you can send your file. :smile2:

[rainwalker]

I have a four-years-old son, and he's ill. I'll be back tomorrow. Thank you again, and happy new year!

Edited December 31, 2006 by Guest