Portal line item values - how to multiply using field value external to portal

[yoyo-joe]

I have a portal that shows the 'ingredients' and quantities required to make/bake/cook a particular recipe; external to the portal I have a field called 'Wish to make' to hold a numeric value variously entered by a user.

How am I able please to multipy the value in each portal row and show the 'sub/total' of each listed ingredient; i.e. each calculated quantity being the full amount of each ingredient needed to make the 'batch quantity' defined by the user?

Thank you for any suggestions.

[comment]

You can add a calculation field to the Ingredients table that multiplies the quantity by the 'number of servings' (?) field*.

However, I am not sure what exactly you mean by "external to the portal".  Is this field defined in the parent Recipes table? I see a potential problem with this: what if  two users view the same recipe, but need different amounts? Properly, this field should be a global field - and then it doesn't really matter in which table it is defined.

--
(*) This is assuming the quantity stored in the Ingredients table is 'per serving'. If not, there should be a field in the recipes table to indicate how many servings is the default, and your calculation field would divide by this field before multiplying by the user-entered number.

 

[yoyo-joe]

wow! that was a quick response, many thanks for your feedback, much appreciated. Taking on board your comments may I re-state my 'difficulty' as i try to get my head around this impressive software.

I have three files, each has one table apiece (to keep things simple for me atm), the three files are:- 1a-ingredients, 2a-recipes, 3a-shoppingList

in 1a each record gives detals, but no quantities, of one ingredient,

in 2a each record assigns a given quantity for an ingredient being used in a particular recipe, hence the 'recipe name' is the uuid and the number of records in 2a for that uuid is determined by the number of ingredients needed by that recipe.

in 3a each record is one particular recipe and the portal (for that record) displays the list of ingredients needed and their respective quantities that will make 'one cake/pie/dish'

MY AIM - while viewing any 3a record I wish to enter a numeric value into some field (on that record) that can be used to multiply the ingredient quantities and display the ensuing results as a 'shopping list' of quantities to be purchased in order to make that numer of 'cakes/pies/dishes.

MY QUESTION - how do I access each ingredient quantity shown in the portal and multiply it by the user value as entered into the 'ingredients multiplier' on that page/record?  Do i need to use a script? or a lookup instruction? or . . . ?

hopefully the above clarifies my enquiry

fwiw - I tried adding a calculation field to 3a but it only found the first portal quantity, not the others, so i'm missing something or doing something awry.

Regards and thanks

[comment]

Two preliminary notes:

6 hours ago, yoyo-joe said:

I have three files, each has one table apiece (to keep things simple for me atm), the three files are:- 1a-ingredients, 2a-recipes, 3a-shoppingList

1. I would suggest you use a single file with 3 tables. That will make things much simpler for you.
2. Please do not use abstract names like 1a, 2a etc. They are very difficult to follow. Give your tables meaningful names - in this case I would use Recipes, Ingredients and Products - and use them in your description.

Now, this 3-table structure is a classic example of a many-to-many relationship: one recipe can use many products, and each product can be used in many recipes. And the way to handle this in a relational database is to have the Ingredients table act as a join table between the two, where each assignment of a product to a recipe is an individual record, with fields for ProductID, RecipeID and the required quantity. So on this front you seem to be on the right path (except for the choice of separate files).

And to your question:
You simply use a calculation field (defined in the ingredients table) to multiply the quantity by the "multiplier" value entered by the user.

I am attaching a simple demo of how this could work. Note that in this example, the default quantity is assumed to be per one serving. As I mentioned in my first post, it doesn't have to be that way, but let's keep it simple for now.

Note also that the Servings field is a global field. This means that whatever the user enters there will be applied to all the Recipes they view (and if your file is hosted, it will default to some value every time they log in). Again, there are alternative solutions to this, but we would need more details before we can consider them.

 

 

CalcQtys.fmp12

Edited March 21 by comment

[yoyo-joe]

hello comment.

Thank You for your detailed reply and for the supplied example file, I sincerely appreciate your time and the knowledge you have shared with me on this topic.

with your feedback and material I am now in a much stronger position to better work with and use this software on an on-going basis

once I've integrated your feedback into my efforts I'd like to close this query with a status of my success as a mark of thanks to you.

regards and best wishes,  yoyo-joe

[yoyo-joe]

hello comment.

with your time , assistance, demo file and my perserverance I have succeeded in my endeavours to build a working 'planner' in FM Pro.

it has been an interesting journey for me, Thank You for your support.

regards, yoyo-joe