Increase value by a percent (Variable)
Any ideas about a calculation that can take the value from a field, then increase it by a percentage (the actual percent taken from a variable) then replace the original field with the new value?
If you are going to use a variable instead of a field, you'd need to use a script step with this calculation:
YourTable::Field * ( 1 + $PctHere )
That's if $PctHere stores the percent increase as a decimal: (%50 = 0.5)
To update a single record, this can be used with the Set Field step.
To update a found set--even every record in a table, this can be used with Replace Field Contents.
If you used a field instead of a variable and the field is not global and defined as a field in the same record as the field with this calculation, you could use:
Self * ( 1 + PctFieldHere )
and clear the "do not replace existing value" check box.
How does this work if the value field is in a portal, and you only want it to be applied to records in the main tables found set.?
I read that as having a set of N records all to be modified using a percent specified in a field in your portal.
You'll need to explain that in more detail. A portal implies multiple values in the related table. How do you determine which value from which related record in the portal to apply to your found set?
Replace Field contents can be used for this, but exactly how you would do that depends on your answer to the above question.
Hope this explains it a little better.
Example: I have product categories that are all listed in the main table. In portal records related to the categories I have the individual products, with a field for their cost. There are multiple products under each category. I would like to apply the percentage increase (Cost Field) for each product (Portal) that is recorded under a selection of categories (Found Set - Main Table).
If each product has a percent increase field, to what records are you applying the percentage? Applying them to the category records does not appear to make sense, but those are the only two sets of records that you have described thus far.
They don't have a percentage increase field, only a cost field. I wish to apply a percentage increase to this cost field that is contained in a portal.
Example.: I search the main table of suppliers who I have listed under the categories of 'Plumbing". The found set has a number of main records all matching the 'Plumbing' category. Under each of these suppliers is a portal listing all their products, including each products cost. i wish to apply a percentage increase to the cost of each product in the found set.
A picky but important detail: your products records are not in any found set in what you describe. Your set of suppliers would appear to be your found set of records. But let's be sure of the details here:
with match fields similar to: Suppliers::__pkSupplierID = Products::_fkSupplierID
is your "category" field in the Suppliers table so you are finding all records in suppliers that are members of a specific category? and then you want to modify all records in Products that are related to any one of the Supplier records in your found set?
If that is correct, then I believe that you can just use replace field contents as I have already described--either manually on a products field located in the portal row or via script where the field need not be visible. Try it on a copy of your database and see. (I just encountered this aspect in one of my projects and since I only wanted to have the Replace field contents modify the records in a portal of the current record rather than the found set, I had to isolate the current record in a found set of one before this only affected the current records set of related portal records.)
Retrieving data ...