0

Minimum cost (coding help)

I’ve written a code in my database using a case by case scenario, so that if option 1 is selected then x amount gets added on to the fitting price etc. I now need to add on to that code so that no matter what option is selected there will be a minimum of 60. I have tried a few different ways, but my code seem to bypass it and it doesn’t work. I also don’t want it to update previous records already created. 
any help would be much appreciated :)

 

if Product.Type != 6 then
    switch Room do
    case 1:
        ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
    case 8:
        ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
    case 7:
        ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
    default:
        'Sq.m' * 'Fitting Rate' * 1.2
    end
else
    'Sq.m' * 'Fitting Rate' * 1.2
end

4 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    Just to verify, are you saying if Product.Type = 1 then the else of:

    'Sq.m' * 'Fitting Rate' * 1.2

    doesn't run?

    or if Product.Type = 6 and Room = 1 then:

    ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5

    doesn't run?

     

     said:
    I also don’t want it to update previous records already created.  any help would be much appreciated

     Well that would be tricky since you are using a formula field and once you change the code it will update all previous records. Unless you write the code in a way that somehow accounts for the old records.

    To be safe you should investigate the switch to a number field that gets filled in by a button/Trigger, so you can be sure old records don't get updated with new formula changes.

      • JamesLIv.1
      • 8 mths ago
      • Reported - view

       no, sorry if my explanation was confusing. The code as it is works perfectly, but I want to create a minimum value. 
      e.g if the default 

      'Sq.m' * 'Fitting Rate' * 1.2

      is less than 60, then make it 60. 
      But obviously even if the coding was correct for that it would alter all existing records. 
      I’ve tried to incorporate it into a “trigger on update” on another field, but it doesn’t seem to work. Can triggers change a field which already has a formula at work?

      • JamesLIv.1
      • 8 mths ago
      • Reported - view

       this is the formula I have in the trigger after update within my product field. So when I select a product all these values are copied from the product and into fields in my form:

      Item := Product.Product;
      'Fitting Rate' := Product.'Fitting Rate (NET)';
      'Retail Price' := Product.'Total Retail Price';
      'Cost Price of Carpet' := Product.'Cost Price (NET)' * 1.2;
      'Product Rate' := Product.'Flooring Retail Price';
      Coverage := Product.'Coverage/Unit (sq.m)'
       

      The initially formula I posted is for a field named “FittingGross”. Is it possible to incorporate a trigger when a product is selected to put a minimum value of 60 into “Fitting Gross”?

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    Can triggers change a field which already has a formula at work?
     said:
    Is it possible to incorporate a trigger when a product is selected to put a minimum value of 60 into “Fitting Gross”?

     Formula field can not be modified by Triggers/Buttons.

    A solution would be to:

    1) rename FittingGross to oldFittingGross

    2) create a new number field called FittingGross

    3) do a bulk edit where you set FittingGross to equal oldFittingGross

    4) add a line to the Trigger to add:

    Item := Product.Product;
'Fitting Rate' := Product.'Fitting Rate (NET)';
'Retail Price' := Product.'Total Retail Price';
'Cost Price of Carpet' := Product.'Cost Price (NET)' * 1.2;
'Product Rate' := Product.'Flooring Retail Price';
Coverage := Product.'Coverage/Unit (sq.m)';
FittingGross := let preCheck := if Product.Type != 6 then
        switch Room do
        case 1:
            ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
        case 8:
            ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
        case 7:
            ceil((('Sq.m' * 'Fitting Rate' + 40) * 1.2) / 5) * 5
        default:
           'Sq.m' * 'Fitting Rate' * 1.2
        end
    else
        'Sq.m' * 'Fitting Rate' * 1.2
    end;
    if preCheck < 60 then 60 else preCheck end

    5) make a few new records to double check that everything works. Then you can delete the oldFittingGross formula field.

    I added a bit to the code:

    I put the value into a variable called preCheck, line 7, then added an if statement, line 21, that checks if it is below 60 and if it is then it returns 60 else it returns the preCheck value.

Login to reply

Content aside

  • 8 mths agoLast active
  • 4Replies
  • 37Views
  • 2 Following