1 Reply Latest reply on Apr 3, 2013 11:38 AM by philmodjunk

    Different Calculations for each Vendor (record)



      Different Calculations for each Vendor (record)



           I am building my first database so bear with me here.  

           I am trying to build a cost estimater for a video editing project I am working on.  Currently I am working with two tables, Clips and Vendors.  I have been able to sucessfully get data on each clip in by exporting a edl file from my editing software and importing it into the clips table.  From the file names of the clips I can determin which vendor it came from and through a relationship I can track which clips came from each.  Also from the in and out times generated from the edl I can determin how long each clip is. So far this is working great and I can see how many total clips or seconds of clips are being used from any single vendor.   

           My problem comes when I try to apply a formula that will multiply out my cost.  Each vendor has a different set of variables to multiply by so I can't make one formula that fits them all.  Instead I need to have a seprate formula for each and store that in the vendors record so it can be used to multiply out the final cost.  

           I guess my question is if there is a way to store a record specific formula as opposed to programing one that would effect the whole table?

           I have tried to store the formula as text in a text field but when I create a calculation field to pull it into I can't recognize it as a calculation just as text.  

           Is this the correct way to go about it?  Any help would be appriciated.  

        • 1. Re: Different Calculations for each Vendor (record)

               Without seeing the formuals used, I can't be sure, but don't give up on the possibility of using a single generalized formula. Not only might it be possible to create a "one size fits all" formula where certain terms can be left blank or default to 1 to get the desired result, you can also enclose both formulas inside an If or Case function that applies the relevant formula based on the value in your VendorID field.

               And you can also store a calculation expression as text in the Vendor table and use the Evaluate function to evaluate it.