3 Replies Latest reply on Jul 18, 2013 11:43 AM by philmodjunk

    help needed with database calculations

    ryandunne

      Title

      help needed with database calculations

      Post

           right i have 3 columns one it an item name the other is a weight and the last is a refference

            

           i need to be able to add a number to the item name and for it to be applies to the weight

           for instance all the weights are filled out by our cart system apart from some direct sales we do where there is no weight inputted we need a way for it to search for a number withing qoutation marks or something im not sure.

           see we can edit the item name on our cart just not input a custom weight and the thing is they will always be custom

           so sometimes it can be 250g or it could be 734g

           screenshot attached

           look forward to hearing back from you thankyou

            

           ryan .

      Untitled.png

        • 1. Re: help needed with database calculations
          philmodjunk

               I think you are describing a situation were each item has a default or "standard" weight--shown in the weight field, but that users frequently need to specify a different weight for the item.

               Why do you need to put the weight into the item name field. That greatly complicates your solution and greatly increases the probability that you will encounter problems due to data entry errors. I can think of two possible alternatives that do not require the user to enter the weight in the item name field:

               1) Make the weight in the weight field an auto-entered value. It can be looked up from a related table, for example. SInce this is an auto-entered value, users can edit the default value with a different weight when needed.

               2) Add another weight field for custom weights. A calculation field can return the custom weight if the field is not empty or the default weight if the custom weight field is empty.

          • 2. Re: help needed with database calculations
            ryandunne

                 it needs to come from the item name because that is the only customizable field that our cart system can use and we cant have an auto calulation because the weights for these types of orders are so different we basically export a csv from the cart import certain feilds into filemaker then export other fields into a royal mails online postage system we understand that this a very complicated procedure that is why weve been struggling to come up with an answer ourselves. cheers

            • 3. Re: help needed with database calculations
              philmodjunk

                   we cant have an auto calulation because the weights for these types of orders are so different

                   I would expect them to be different from each item and the auto-enter field options used can enter the value that is appropriate for each item via a table of such values linked in a relationship to your existing table.

                   If the weight is the last item in the field in every case, this calculation could extract that value from the field:

                   GetAsNumber ( RightWords ( Yourtable::Item Name ) ; 1 ) )

                   In some cases, GetAsNumber will not be needed. Using it will remove the units to the right of the number.