2 Replies Latest reply on Jun 25, 2013 7:33 PM by BorioMarco

    calculation field in related table doesn't update the result

    BorioMarco

      Title

      calculation field in related table doesn't update the result

      Post

           Hi everybody,

           i have a problem with my first database..... it's just a two table DB with one relationship, but i'm trying to learn how to make it work properly.

           In my first table (called orders) I have several fields (salesmen, product, amount, invoice number etc) and in the second table i would like to get a report about the turn-over of one specified salesmen (sub-total for product families and grand total).

           I've set a Salesmen_ID in my main Table, sub-totals for Product family are calculated in fields under the same Table.

           In the second Table (called example Mike as one of the salesmen) I've set the Salesmen_ID and create the relationship with Order Table, than I've the fields for the sub-totals (data coming from the Order table records) and a new field calculating the grand total.

           And here is where i get my problem...

           Everything seams to work fine, the data are correctly taken from the records of Order table, but.... The data in "Mike" table updates their values ONLY if i first select the Salesmen_ID field and press TAB.... than the data are updating with the new values. 

           If i go back to Orders, modify some record (change amount or change product family), when i go back to "mike" table the data are not updated, i still have to select the Salesmen_ID field and press TAB.

           There is a way to make those field update automatically??? 

           I think the main problem is that I'm just trying, I had this on an excel file and I'm trying to make it on FM. 

           It might be the way i set the relationship or just I'm missing something.....  

           HELP!!!!!  Thanks for you reply...

            

           Marco

        • 1. Re: calculation field in related table doesn't update the result
          philmodjunk
               

                    In the second Table (called example Mike as one of the salesmen)

               I hope that doesn't mean that you plan on separate tables for each salesperson--not a good idea! Use one table for all your salespersonnel.

               You appear to have this relationship

               Orders>-----Mike

               Orders::SalesPersonID = Mike::SalesPersonID

               and have defined something that is supposed to calculate a total from Orders for a given record in "Mike"...

               But there is more than one way to do that. Exactly how did you set that up?

               If you are using an auto-enter calcualtion on a number field, you are correct that this will not automatically update. You should use a field of type calculation defined in Mike or a summary field defined in Orders to compute the total.

          • 2. Re: calculation field in related table doesn't update the result
            BorioMarco

                 Dear Phil,

                 many thanks for your reply. You're right, i changed the field type to calculation and now it updates correctly.

                 back to the concept of my first DB in FMP (and first overall as i never used any other DB builders)..

                 Several years ago i create an excel worksheet for keep records of orders and fill automatically the result and bonus sheets for each salesmen.

                 It works, but I wish to learn how to make something similar in FMP for mainly two aspects: Layouts and more easy access to the records.

                 I'm sure the way i started to build this DB is not even closer to the worse case, but i don't know how to use the power that FMP might offer.

                  

                 You said that make one table for each salesmen is a bad idea, and i agree with you. For sure one table only that display the data related to the selected salesmen if for sure more efficient, but how to do that?? As each salesmen might have different conditions and targets. 

                 Maybe my approach is totally wrong... frown

                 For make the calculations i create in the Orders table many numeric fields with conditional calculations, in order to get subtotals divided salesmen by salesmen, product by product.... among the field related to the order (like date of order, customer number etc), the variables i need to track are:
                 Salesmen
                 Product family
                 Amount
                 Order confirmed by downpayment (Y/N)
                 Invoiced (Y/N)
                 the last two (Y/N) are the key factors that are going to modify their order turnover and invoiced turnover, and calculate the related bonus for them.

                 so for each record there are several fields calculating the result of the conditions

                 for example: for each salesmen i have one field with a formula like this

                 if (SM_ID=1 and prod_fam="AC" and conf_ord="Y" ; amount ; 0)

                 to calculate if this order goes to the order turnover of which salesmen.

                  

                 I'm sure there is a better way to do that....

                  

                 Thanks for your help

                 Marco