Please describe the details of your valud list set up. Is this values list set up with the "use values from a field" option? Does the that's the source of values for the value list have more than one record with the same value?
Thank you Phil,
Yes, the value list is set up to use the values from a field option. And yes, the source of the values has more than one record with the same value. I would like to pull the latest record from the source database rather than the default action which pulls the first record of records having the same value.
So the problem is not with the value list, it's with the lookup process that then pulls data from the related table?
If so, is there a serial number field defined in this table of related records?
If so, you can open Manage | Database | relationships, double click the relationship line to open a dialog where you can specify a sort order for the related records. Specify a sort order that sorts the records in descending order by this serial number field. This will make the most recent related record the "first" record in the relationship. You may need to make additional modifications if this change adversely affects other parts of your solution. It will, for example, change the order of records in a portal based on the same relationship.
Every record has a price and date attached to it. I have tried to specify a sort order for the relationship. There is a date field in one of the tables and I sorted the relationship in descending order by the date field in order to capture the most recent record and the lookup process still displays the first record rather than the most recent record. Perhaps I have set up the relationship incorrectly or the value list or both! I specified the sort order in the table that has the date field.
thank you for your help
You appear to be having problems posting to this forum. What device/browser are you using?
Please describe the relationship you have defined for this and how you have set up your layout to work with the value list and look up fields.
I have 2 databases/tables. One is called "Cost" the other is "Supplier Cost". I update Supplier Cost with food product pricing. I use the Cost database to "cost out" recipes. Each recipe contains up to 16 food product items. There are 16 "Ingredient" fields in the Cost database. Each Ingredient constitutes a record consisting of the ingredient name, the supplier name, the cost and the amount for the ingredient. In the Supplier Cost database I have a Product field, a Cost Date field, and a Product Cost field. Each time I get new pricing for a Product, I update the Supplier Cost database. I created relationships between each "Ingredient" field from the Cost database and "Product" in the Supplier Cost database. I ended up with a new table for each relationship. Ingredient1 related to Product created Table "Supplier Cost", Ingredient2 related to Product created table Supplier Cost2, Ingredient3 related to Product created table Supplier Cost3 and so on. I created a value list in Supplier Cost called "Product from Supplier Cost Value List" that gets its values from the Product field and the Cost Date field. In the Cost database I set up each Ingredient field as a member of a value list -- and selected the "Product from Supplier Cost Value List" as the value list.
I'm using Firefox
Hmmm, where to start on all that... You have significant structural issues that greatly multiply the work needed to make any change at all to the design of your database.
I suggest the following structure or one similar to it:
Recipes::RecipeID = Recipe_Ingredient::RecipeID
Ingredients::IngredientID = Recipe_Ingredient::IngredientID
Ingredients::IngredientID = Suppliercost::IngredientID AND
Ingredients::SupplierID = Suppliercost::SupplierID (I'm assuming that you might have more than one supplier for the same ingredient here.)
A given ingredient can be used in many recipes and a recipe can list many ingredients. Please note that this structure can support any number of ingredients as adding more ingredients is simply a matter of adding more records. There is no need for creating a separate relationship for each ingredient if you use this structure, a single relationship link works for all of them.
With this setup, assuming that you have fields of type date and not text for the date field, you can sort SuplierCost in the Ingredients to SupplierCost relationship by date in descending order (default is ascending so you have to change the sort option here).
Then this calculation in Recipe_Ingredient can compute the ingredient cost for a given recipe:
Qty * SuplierCost::CostAmount
(You put Qty and this calculation in Recipe_Ingredient so that you can specify different quantities of the same ingredient for different recipes.
Can you please breakdown the following line from post?: Recipes-----<Recipe_Ingredient>-----Ingredients----<SupplierCost
What do these refer to? Is Recipes a new table? Is <Recipe_Ingredient>a table or a field?
I am not familiar with the database lingo, is it possible for you to explain it to me like I'm a two year-old? I have some database experience. (Self-taught) And I grasp the basic concepts.
These are all occurrences of tables of the same name as those used in this diagram. Think of it as a short hand sketch of what you'd set up in Manage | database | relationships. ----< stands for a 'one to many' relationship.
I like your suggestion for the structure and I understand the benefits -- if you could walk me thru the set-up I would greatly appreciate it. I am struggling to relate the shorthand to the object. Am I correct in that there are to be three tables - Recipes, Ingredients and SupplierCost?
I am not sure what Recipe_Ingredient is. Is that a reference to an Ingredient field in the Recipe table?
There would be 4 tables. Recipe_Ingredient is also a table.
Where you see this type of notation:
Recipes::RecipeID = Recipe_Ingredient::RecipeID
The name to the left of the :: is the name of the table and the name to the right of :: is the name of a field defined in that table.
This expression tells you that you need to link the Recipes table to the Recipe_Ingredient table by fields named RecipeID. You can do that in Manage | Database | Relationships by dragging from the RecipeID field in one table to the RecipeId field in the other. You can, of course, use your own names for these tables and fields.
Recipe_Ingredient is a special kind of table referred to as a "join" table. It facilitates a many to many relationship. In this case it, allows you to link many different records in Recipes to the same Ingredient and many different Ingredients to the same recipe.
You want to add "Granulated Sugar" to the list of ingredients for a specific recipe with a recipeID number of 2500. Let's say Granulated Sugar has an Ingredient record with an IngredientID of 23.
To do this, you'd create a new record in Recipe_Ingredient. You'd assign 2500 to its RecipeID field to link it to that recipe record and assign it an IngredientID of 23 to link it to the Ingredient Record for granulated sugar.
This is typically managed by enabling "allow creation of records via this relationship" for Recipe_Ingredient in the Recipes to Recipe_Ingredient relationship and then placing a portal to Recipe_Ingredient on your Recipes layout in order to list all ingredients used with that recipe. You can put the Recipe_Ingredient::IngredientID field in this portal formatted as a drop down list or pop up menu of Ingredient IDs and their names to enable you to add ingredients to the list for a given Recipe record.
PS. I have deliberately used "Table" here in this post where I am usually referring to "table occurrences". Table Occurrences are the "boxes" found on the relationships tab in Manage | Database. You start out with one occurrence box for every table you define and it has exactly the same name as your table, but you can add additional occurrence boxes to the same table if such are needed to define additional relationships between the same two tables and also to avoid a "circular relationship" between table occurrences.
To learn more about table occurrences, see this link: Tutorial: What are Table Occurrences?
I think I may need another definition or clarification. I created 4 databases with the names you specified and then set the relationships as you specified in the Recipe_Ingredient database. After reading the Table Occurrences tutorial I'm wondering if I was supposed to create one database and then define 4 tables within that database. I can do that. I recently acquired Filemaker Pro 11 Advanced. Previously I was using Filemaker 6. So all this is new to me. Does it matter what table I begin with -- for example should the database be Recipe_Ingredient or does it matter. They way I did it, it looks like I would have to define each of the relationships between each database. It looks to me with Filemaker 11, one database can contain multiple tables. That wasn't the case with Filemaker 6. I will begin working on the one database with 4 tables. I do need to know which ID fields need to be serial ID fields.
While you can get this to work with one table to a file. It will be simpler for you to put all the tables into one file. The name of the file need not be the name of any table in your database, but the default name of the first table you create will match the file name unless you rename that table.
Recipes::RecipeID and Ingredients::IngredientID will be serial number fields. The naming convention I used was if the field name matches the table name, it's a serial number field and if it does not (Recipe_Ingredient::RecipeID for example), then it's a simple number field.
I have 4 tables in one database, tables named as you suggested. I have set the relationships as you suggested. What table do I use to input data? I have data that I would like to import from another table. Also I need to be able to do more calculations than what I previously indicated. For each ingredient I need to get a unit cost and a cost based on the amount of the ingredient. I'm working on the portal.