8 Replies Latest reply on Aug 21, 2012 9:56 AM by Michelle_1

    How can i store the results of  calculations in Variables?

    Michelle_1

      Title

      How can i store the results of  calculations in Variables?

      Post

      Hello Experts!

      How can i store the results of calculations or Summary Fields in Variables?

      affff its all so complicated :(

      i have 2 Tables. Table 1 is where i type all the orders for all the workers from all custumers.

      on this table i have calculated fields that shows me how much they should earn as comission.

      I have a Script that shows me betwen a time range what and how much one of my workers should earn (for this time range) and shows me all on a Report based on this table (Table 1)

       

      I have then another table, "table 2"

      on this table i want a new record, that should show the results i got on my Report (based on table 1!!):

      Record nr:________

      Worker Nr:_________

      Date:__________

      Ammount of commission: ________________ from Date 1__________ to Date 2_________

      Ammount of tax:_________

       

      The fields "worker nr." ; "Date"; "date 1" and "date 2" works perfectly. Just the Summary and calculated fields wont.

      I get then the total ammount "ever" but not sorted by worker or time range....

       

      I wonder then how i could index those calculated fields or just "grab" the results generated by my script and put it on my new table....

      I really really MUST get it done and appreciate any help!

      tx!

      Michelle

        • 1. Re: How can i store the results of  calculations in Variables?
          schamblee

          If you got the results in one table, then why do you need a second table.  You can have multiple layout based 1 table ?

          You can use set field in a script to insert data into a field.  You can copy from one field to another using this command.  There no reason to double or triple track information.  You could link the record together with a id number.

          • 2. Re: How can i store the results of  calculations in Variables?
            Michelle_1

            Hello S Chamblee,

            Thank you very much for your answer, sorry i am really a very new user of FM,

            I can have multiple layouts based on 1 Table,

            But then how would i save my report?

            see, i make one report once in the month for each worker.

            I wanna have then the reports saved somehow...in a way that they wont change if i make changes on my records or whatever... I need always to calculate the results from the last month together with the current month, if one worker, for example had a negative commission in the last month....

            What you mean by link the record together with a id number?

             

            Tx

            Michelle

            • 3. Re: How can i store the results of  calculations in Variables?
              schamblee

              You create a field and call it "employeeid" or something like that.   This would be in both tables then you would create a relationship between the two tables based on this relationship.   You would have like one table called employees and another called reports.  The report table would have the information you stated above plus the date, so you could lookup a previous report.  You can also save reports to PDF. 

                There are several good books available from amazon about filemaker.  I really like "the missing manual"

              • 4. Re: How can i store the results of  calculations in Variables?
                Michelle_1

                I already have such fields and relationships, and as i explain above, all fields work fine but the calculated ones, there must be something i am missing or forgetting, or something else i should do...

                I also save my reports as pdf, all fine and good...

                I would be happy to buy the book, but i am in a reall hurry here, must deliver it done till 1 September. It was not supposed to be so colplicated, its a simple solution for a small company. I am not a developer, just make it to help my work to flow better.I am also always trilled to learn new stuf, so i can say i know abit of everything :)

                 

                Tx

                Michelle

                 

                • 5. Re: How can i store the results of  calculations in Variables?
                  philmodjunk

                  If your data on which the calculation is based hasn't changed, you can pull up the same report anytime you need to without having to store the results in another table.

                  • 6. Re: How can i store the results of  calculations in Variables?
                    Michelle_1

                    Hello! tahnk you for your attention,

                     

                    I know i could always repeat the search, the problem is:

                    I change the Status of the orders. Many custommers cancel their Orders. That generates another problem that is: When one worker sold

                    In January: 100 Contrachts i paid the worker all 100.

                    In february Sold 20 but got 30 cancellation from January. He would then get in February a negative Bill.

                    then in March he sold again 100 but i need to get those 20 back!

                    There must be a field in the Invoice that when the last Bill is negative then it must calculate over the new values.

                    I just need the values to poup up in the new table! Its making me craazzy! i tried so many things...

                    looks like its a really "hairry" thing to get done! huhu

                     

                    Michelle

                    • 7. Re: How can i store the results of  calculations in Variables?
                      philmodjunk

                      What you want is actually pretty easy to do. It's just not something I would recommend doing unless there's good reason to do so. You've now posted that "good reason". (And it's why I posted the comment that I did.)

                      I can only describe this in general terms as I have no idea how your database is designed.

                      You can write a script like this:

                      #Go to layout based on your current table
                      #perform your find
                      #Sort your records
                      Set Variable [$value1; value: YourTable::Field1]
                      Set Variable[$Value2; value: YourtTable::Field2]
                      Go to Layout [CommissionsLayout  (Commissions) ]
                      New Record/Request
                      Set Field [Commissions::Field1 ; $Value1]
                      Set Field [Commissions::Field2 ; $Value2]

                      It may also be possible to create a record and set values in fields without having to copy them into variables and change layouts--but that approach requires a specific relationship with the "allow creation..." option enabled and that may not be possible with your data.

                      • 8. Re: How can i store the results of  calculations in Variables?
                        Michelle_1

                        OHmygod! it works!

                        You like a FM Hero!

                        I was always making the script worng!

                        First mistake, i forgot the step "Go to Layout [CommissionsLayout  (Commissions) ]

                        Second mistake: in my fields on the new Layout i had "Display data from" my Main Table and not from te actual Layout!

                         

                        Thank you very very much!