Thread: Storing Stock Levels / Calculated Fields

Well, it's the amount of product produced on each batch.

So, I think bulk stock calculation should be something like:-

Code:

SUM(tblproductbatch.Weight)-SUM(tblbatchweights.Amount)

The slight issue being that the amounts in the batch weights will be stored with a scale as well, so it might be 5mg or 500ug.

So, will have be some sort of scaling up on the weights to calculate bulk stock remaining.

Does that make sense?

The reason I asked is because I'm looking for the place to best relate to each office. Anyway, I wouldn't store the weights with different units of measure. They should all be the same unit, and then be converted with a function. For example, when I create a table for attachments and I want to store the file size, I store it as bytes. Then, I have a function in .Net that, when a file reaches a certain amount of bytes, converts the value to either kb, mb, or gb. For calculation purposes, you would want to do the same type of thing here.

Originally Posted by jmurrayhead

The reason I asked is because I'm looking for the place to best relate to each office. Anyway, I wouldn't store the weights with different units of measure. They should all be the same unit, and then be converted with a function. For example, when I create a table for attachments and I want to store the file size, I store it as bytes. Then, I have a function in .Net that, when a file reaches a certain amount of bytes, converts the value to either kb, mb, or gb. For calculation purposes, you would want to do the same type of thing here.

Yeah, I did think about that initially, but then I decided to include the scale side to avoid confusion when the user adds a product and weight. Each product is sold in different weights and currencies and I wanted the user to be certain what weight and scale they were adding. Also micro grams use an html code to produce the symbol, so I had to store that against the scale element as well.

If I was to go down the conversion route and store all weights in the same scale, would you go for the lowest or highest denominator? ie store 1g as 100mg or store 100mg as 0.100g? Or whatever the correct decimals should be..

I think the range goes from micro grams to grams. Although eventually they may want to store product routes (recipes) which may involve storing raw materials and weights (I guess up to kgs).

I guess I'd have to come up with a function that converted a decimal weight into a user friendly version. Eg 0.000005g would be 500ug, I think.. How do you normally write something like that? Keep dividing by 100 in a loop until you have a whole number? and then record how many loops it takes to give you the scale?

Originally Posted by richyrich

Yeah, I did think about that initially, but then I decided to include the scale side to avoid confusion when the user adds a product and weight. Each product is sold in different weights and currencies and I wanted the user to be certain what weight and scale they were adding. Also micro grams use an html code to produce the symbol, so I had to store that against the scale element as well.

Let them enter it any way they want to. They can select the scale and then enter the value. After they submit the data, convert it.

Originally Posted by richyrich

If I was to go down the conversion route and store all weights in the same scale, would you go for the lowest or highest denominator? ie store 1g as 100mg or store 100mg as 0.100g? Or whatever the correct decimals should be..

I would probably store as the lowest possible value that would make sense. Using my previous attachments example, it wouldn't make sense to store smaller than bytes.

Originally Posted by richyrich

I guess I'd have to come up with a function that converted a decimal weight into a user friendly version. Eg 0.000005g would be 500ug, I think.. How do you normally write something like that? Keep dividing by 100 in a loop until you have a whole number? and then record how many loops it takes to give you the scale?

A simple If...Then would work for this. Here's an example of the function I use for converting file sizes:

Code:

    Public Function FormatBytes(ByVal bytes As Integer) As String
        If bytes >= 1073741824 Then
            Return Format(bytes / 1024 / 1024 / 1024, "#0.00") & " GB"
        ElseIf bytes >= 1048576 Then
            Return Format(bytes / 1024 / 1024, "#0.00") & " MB"
        ElseIf bytes >= 1024 Then
            Return Format(bytes / 1024, "#0.00") & " KB"
        ElseIf bytes < 1024 Then
            Return Fix(bytes) & " Bytes"
        Else
            Return "0 Bytes"
        End If
    End Function

Originally Posted by jmurrayhead

Let them enter it any way they want to. They can select the scale and then enter the value. After they submit the data, convert it.

I would probably store as the lowest possible value that would make sense. Using my previous attachments example, it wouldn't make sense to store smaller than bytes.

So, store everything as micro grams (I don't think they'll go smaller than this) and then still use the scale table, but maybe have a multiplier as one of the fields so it's easier to multiply up what they've entered?

Originally Posted by jmurrayhead

A simple If...Then would work for this. Here's an example of the function I use for converting file sizes:

Code:

    Public Function FormatBytes(ByVal bytes As Integer) As String
        If bytes >= 1073741824 Then
            Return Format(bytes / 1024 / 1024 / 1024, "#0.00") & " GB"
        ElseIf bytes >= 1048576 Then
            Return Format(bytes / 1024 / 1024, "#0.00") & " MB"
        ElseIf bytes >= 1024 Then
            Return Format(bytes / 1024, "#0.00") & " KB"
        ElseIf bytes < 1024 Then
            Return Fix(bytes) & " Bytes"
        Else
            Return "0 Bytes"
        End If
    End Function

Or just store the multiplier as a field in the scale table?

You could store it as micrograms, I might store it as grams, myself, but either way you'll be able to convert. Not sure what you mean by having a multiplier for a field, though...

Originally Posted by jmurrayhead

You could store it as micrograms, I might store it as grams, myself, but either way you'll be able to convert. Not sure what you mean by having a multiplier for a field, though...

Assuming I had a table of scales (mg, ug etc.) the multiplier would be whatever I needed to multiply it by to get to the standard weight measurement. ie if I was to store in ug, the multiplier for mg would be 1000. ie if a user enters 500ug I would have to do something to convert it to the standard weight measurement I decided to use.

oh ok, yeah, I suppose that would work fine.

Your multiplier could be stored in the weights table. Just add a column so that each weight has an indicator of how many micrograms said weight type represents. Then you can multiply or divide by that number when a user enters a batch amount (they could perhaps select the unit of measurment when entering the data).

One more thing - will this always be dried goods? You won't need microliters right?

Originally Posted by Lauramc

Your multiplier could be stored in the weights table. Just add a column so that each weight has an indicator of how many micrograms said weight type represents. Then you can multiply or divide by that number when a user enters a batch amount (they could perhaps select the unit of measurment when entering the data).

One more thing - will this always be dried goods? You won't need microliters right?

I'm thinking store the weight amount and the scaleId. In theory the products are sold in specific set weights (1mg, 500ug etc.) although there will need to be the option to enter a bulk weight, but again this can be referenced against a scale.

That's a good question laura... They are all dried products, although they are sold in weights that will given a certain strength solution when dissolved. I think this will just be adding a weight the product can be sold in though (ie 2.345mg will give a certain concentration when dissolved in x amount of solvent.