Relation ships lookup

[tmas73]

I want to have a portal with products and weights. That portal menu takes the info from the product list but the values in the product table should show up also in the data table. It makes more sense to look at the test file to understand what I'll try to do.

Is it possible to have Value1 and Value2 showing up from the product table in the data table.

relation.fp7.zip

[tmas73]

No responses? Let me explain better maybe that helps.

Hi

The idea should be that a user can add its own values to a list of products (product table, value 1 and value 2), then in the main table he can add the products in the main table portal. At the end I would have in the data table the chosen products with their Value1 and Value2 (from the product table) for a calculation. So basicly the Value one and Value2 should always stay with the product (copy from product table to data table) so I can calculate a formula in the data field that would not be visible to the user.

[David Jondreau]

What is the information in the Data table supposed to represent? What is a record in the Main table supposed to represent?

[tmas73]

What is the information in the Data table supposed to represent? What is a record in the Main table supposed to represent?

It suppose to represent the same value like in the products table. I previously try to make a lookup value but that did not work.

[David Jondreau]

No, I mean what IS it? "Value" and "Data" are meaningless terms. "Weight" and "Product", for example, are not.

Also, what is the purpose of the "Main" table? And why are you duplicating values?

What I'm getting at is that your database likely has structural problems that are preventing you from achieving the interface you want. Your difficulty in getting lookups to work reinforces this.

Posting a sample file will likely get you the best results.

[tmas73]

No, I mean what IS it? "Value" and "Data" are meaningless terms. "Weight" and "Product", for example, are not.

Also, what is the purpose of the "Main" table? And why are you duplicating values?

What I'm getting at is that your database likely has structural problems that are preventing you from achieving the interface you want. Your difficulty in getting lookups to work reinforces this.

Posting a sample file will likely get you the best results.

Thanks for the help, this is not a easy task for me mathematically and logically. I asked prior on a different post and I got so far. Now I made a slimmed down version so I can concentrate of the one issue I have. And also i dont have to post the project. If you want me to send you the file I can do that, I just dont want it accessible to everyone. Here the post from the other topic.

This is a slimmed down version of a oil calculator. Link

Main Table:

The main table has recipes and information how to make soap. (Choosing ingredients oils etc.) There is also the portal with popup to display oils from the oils list (product table).

Product Table

Each oil has 2 SAP values (saponification value). That is already entered in the big list of oils in the product table.

Data Table

The Data table just keeps the info for the recipes and the weight of the oils to calculate the SAP Value.

If you want me to pm the original file let me know.

[comment]

I would say that your "Product" table is a table of Materials (or Oils), and your "Data" table could be called Quantities (or Ingredients). Your "Main" table is either a table of Soaps, or a Calculator - it depends on whether you keep a record there for each soap you create, or do you use just a single record to make temporary calculations.

In any case, your Quantities table should have a MaterialID field (where you select the material to be included in the soap). If you then place fields from the Materials table inside the portal, they will show data from the selected record in Materials.

[tmas73]

I would say that your "Product" table is a table of Materials (or Oils), and your "Data" table could be called Quantities (or Ingredients). Your "Main" table is either a table of Soaps, or a Calculator - it depends on whether you keep a record there for each soap you create, or do you use just a single record to make temporary calculations.

In any case, your Quantities table should have a MaterialID field (where you select the material to be included in the soap). If you then place fields from the Materials table inside the portal, they will show data from the selected record in Materials.

How would the relation ships look like? I added the field from the products (materials) but its empty.

[comment]

The relationships would look like in any many-to-many relationship. After all, the Quantities table is merely a join table between Soaps (or Recipes, if you like) and Materials, so:

Recipes -< Quantities:

Recipes::RecipeID = Quantities::RecipeID

Materials -< Quantities:

Materials::MaterialID = Quantities::MaterialID

As you can see, the Quantities table has two foreign keys - one for each parent table.

[tmas73]

The relationships would look like in any many-to-many relationship. After all, the Quantities table is merely a join table between Soaps (or Recipes, if you like) and Materials, so:

Recipes -< Quantities:

Recipes::RecipeID = Quantities::RecipeID

Materials -< Quantities:

Materials::MaterialID = Quantities::MaterialID

As you can see, the Quantities table has two foreign keys - one for each parent table.

Wow, sorry but I dont understand.

Ok you are talking about tables right?

So the quantities table connects to recipes ID table and Materials ID table, or?

Edited April 22, 2008 by Guest
Changed picture "

[comment]

See if the attached helps. Note that you can place ANY field from Materials in the portal, and it will show data from the correct record.

Recipes.fp7.zip

[tmas73]

See if the attached helps. Note that you can place ANY field from Materials in the portal, and it will show data from the correct record.

That helped thanks, is there a way to use the same strategy but without seeing the Material ID in Recipes? I try to remove the Material ID from the value list but this wont work.

Thanks

[comment]

Set the value list to show values only from second field, and change the drop-down to a pop-up.

[tmas73]

Set the value list to show values only from second field, and change the drop-down to a pop-up.

Ok I tried to recreate the file. But I run into problems. If you could have another look at this file would help. Sorry for the annoyance. But since I start using FM and thats a while ago, I always have problems with relationships.

opas_0.9_slimmed.fp7.zip

[comment]

Your file is password-protected. See this one:

Recipes2.fp7.zip

[tmas73]

Your file is password-protected. See this one:

Sorry its "Developer"!

[tmas73]

Your file is password-protected. See this one:

Thanks for your patience I got it to work. Thanks!