Mike Benner: Quick, Write That File I Need To Include It

Quick, Write That File I Need To Include It

Mar 19 by Michael Benner

I ran into an interesting situation today and all I can say about my solution is became quite unique (I think). We will do this backwards...The solution, build a query from a query and some conditional statements, then generate a file from that output and then in the very next line of code include that new file to perform a calculation.

Now that said

I hope it can be done a different way, but by the time I got this far my head hurt and this was all I could come up with.

Here is the situation, I have a table of items and that table links to a table of item attributes. Those attributes can be text, numeric, a list, or a boolean. They can also either just be one of those values or they can have a price (or prices in the case of the list) associated with them. Those items belong to a category that contains a pricing formula, freight formula and three quantity formulas for all items in that category and uses the attributes (both raw value and the price). Below is the query that ultimately stores the information on hand to be calculated.

Then I get the formula (also stored in the DB as it is user defined) and write that formula to a page inside an output of the above query that contains the values the formula needs.

The avearge formula is something like:

but occasionally I have run into:

and so far this has worked well. Now I am aware of files being overwritten, loads this puts on the system and just the lack of scaling well, but I am hoping in the next few days, as the fog leaves my head, I will find a cleaner solution or at least tighten this one up.

While I am happy I was able to actually solve this problem and make it work, it is not as nice as I wanted or hoped. So if anyone has any comments on this solution I would love to hear them.

A few statements need to be made. The database is set. I inherited it this way and it is populated with thousands of items each with 20+ attributes and the formulas are dynamic (users can change them when they want, although they are usually the same for months at a time) so storing a copy of the file with an option reset could be used to tighten it up a little, but I am looking to avoid going to the file system all together.