Jump to content

Find related records


rainwalker
 Share

This topic is 5750 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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...

Link to comment
Share on other sites

Hi rainwalker, welcome to FM Forums! :smile2:

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 by Guest
Added Update
Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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 by Guest
Added Update
Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5750 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.