You seem to be missing a table and there is no link described for your current pair of tables to link by store.
It would seem that you need the following tables:
Stores::__pkStoreID = Ratings::_fkStoreID
Fruit::__pkFruitID = Ratings::_fkFruitID
You can place a portal to Ratings on the Stores layout to list and select Fruit records for each given Stores record. Your rating field would be a number field defined in Ratings. Fields from Fruit can be included in the Portal to show additional info about each selected Fruit record and the _fkFruitID field can be set up with a value list for selecting Fruit records by their ID field.
With this set up. You can create a summary report layout based on Ratings where you can perform a find for all ratings >= 3 and sort them by store to group the fruit ratings by store. A sub summary layout part can show info about each store and you can include fields from Fruits in the body to add info about each rated fruit.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
I am not sure if I am understanding correctly. I believe I have setup according to your suggestion. Please see accompanying image.
Am I missing something?. Where do I get the 1 to 4 values for the Fruit ranking?
To repeat from my last post, you would add a rating field to the Ratings table for rating a specific type of fruit at a specific market.
I have created a Rate Field with custom lookup values (1-4) in the Ratings table.Please see image. I do not know how to exactly hook it u between the Stores and Fruits table (although I have created a relationship (as you suggested) and really do not know if it is the correct relationship
What exactly do you mean by creating a Portal?
Put a portal to Ratings on your Stores field. Make sure that "allow creation of records via this relationship" is enabled for Ratings in the stores to ratings relationship. (Double click the relationship line)
In the portal row include at least the Rating field and the Fruit ID field. You may need to also include the fruit name field in this portal row. Wen you select a Fruit ID, you create a new ratings record and link the current store to the selected fruit. You then enter or select a value for the rating in the same portal row.
You only need the Fruits table if you need to record additional info about the same fruit offered for sale at multiple stores.
Good day PhilModJunk.
Thank you for the follow up
I followed your last suggestion but I don't think I have manage to get it to work.
I add the Portal and enabled "allow creation of records via this relationship" But I do not get the result
You have enabled "allow creation..." for Stores instead of for Ratings.