7 Replies Latest reply on Feb 18, 2010 3:58 PM by liquidcooled

    Calculation using data from a specific record not relational

    liquidcooled

      Title

      Calculation using data from a specific record not relational

      Post

      Hello, 

       I'm trying to add a calculation that references a specific record in a table. It's always the same field in that same record that the calculation needs to reference. How to I make an auto enter calculation that finds a specific field in a specific record.

      Thanks 

        • 1. Re: Calculation using data from a specific record not relational
          ninja
            

          Howdy,

           

          You don't give much detail about "Why?" so it's hard to tell if this suggestion is appropriate:

           

          I have a 'Constants' table that I use for 'constants' that actually might change every so often.  It has one record only, and holds values in global fields for reference from wherever in the Dbase.  Would such an approach work for you?

          • 2. Re: Calculation using data from a specific record not relational
            liquidcooled
               I have data imported from Quickbooks into my database. I have a table of service items and fees that are imported into a duplicate table in FM. This table is updated thru a script from Qucikbooks into Filemaker. Some types of order has specific fees that are related to a fee Item in QB. I want to have these fees automatically update on the order when the fee rate changes in QB. Since my list is already updated in FM I just need to connect the specific fee record to my Sales order in FM.
            • 3. Re: Calculation using data from a specific record not relational
              ninja
                

              If I understand correctly, you would simply need a "Fees" table in FMP with a single record.

               

              Then you could join this Table to your SalesOrder Table using the [X] operator so that that single record becomes joined to all records in your SalesOrder table.  Now you can reference your set fees for autoentry calculations as you asked.

               

              If your fees change, simply change the values in the single record in your Fees Table.

               

              Is that what you're after?

              • 4. Re: Calculation using data from a specific record not relational
                liquidcooled
                  

                I don't know how the x operator works, but I don't think that is the solution. What I have is a table that has fees and services. My sales order uses 1 specific fee for all orders. The complication is that the fees are imported on a regular basis from another database that I don't have control over. So what I need is a reference to a specific record in the fees table. I have a unique id for all the fees in this table I just need to reference it so that if the value changes in the other database It will automatically change in the fees table as well as the SO table where it is used to calculate total price. I'm guessing there is a really simple way to do this, I'm new to FM and pretty bad at writing calculations and scripts. 

                 

                • 5. Re: Calculation using data from a specific record not relational
                  ninja
                    

                  Let's make sure...

                   


                  liquidcooled wrote:

                  What I have is a table that has fees and services.

                  My sales order uses 1 specific fee for all orders.

                   

                  The complication is that the fees are imported on a regular basis from another database that I don't have control over.


                  So there is one specific fee for all of TODAY'S orders...but the fee might change on a regular basis (ie. frequently) ?

                  If this is so, you are right, the [X] join is probably not appropriate.

                   

                  If each new fee set is a new record in a Fees Table...how do you know which record to use?  If you were doing it manually...how would you know which record to pull up?  What criteria define this record as the 'right' one?

                  {three of the same question...}


                  • 6. Re: Calculation using data from a specific record not relational
                    liquidcooled
                      

                    There is alway only one record for each type of order. Lets just say there is only one correct record. I know what the Primary key is of that 1 record. The problem is that the price is updated from an external table. So I need a field in my sales order table / layout that always references the same fee / primary key. The only thing is that the price of that fee may change when it changes in the external database but inside my FM database the fee and primary key are always the same. 

                    Thanks for helping me with this, sorry I'm not explaining my self well : ) 

                    • 7. Re: Calculation using data from a specific record not relational
                      liquidcooled
                         Well I'm not sure it this is the best way to do this but what I did was to create a script that runs after the data import script. The script searches or each of the different Primary keys for each of the fee items. It then goes to my globals table and sets the value there where I can access it from any of my layouts.