1 2 3 Previous Next 33 Replies Latest reply on Apr 11, 2014 10:33 AM by philmodjunk

    Finding the difference of the field between current record and previous one

    JohnAbenaim

      Title

      Finding the difference of the field between current record and previous one

      Post

           My project is as follow: 

           I want to find out the difference between the current record and previous one to calculate the net balance so that i can charge the correct amount to clients. 

           My database has 2 tables: Client & Transaction

           1. Client

             
      1.           Client ID (Auto Entered Serial)
      2.      
      3.           Client Name (Text)

           2. Transaction

             
      1.           Date (date field)
      2.      
      3.           Client ID (Number)
      4.      
      5.           Current Balance prints (Number)  (amount printed since client was added on the printer)
      6.      
      7.           Lookup prints (calculation)  = Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; Balance of BW - GetNthRecord (Balance of BW ; N - 1 )  ; "" ) )
      8.      
      9.           Net Prints (calculation) = Balance of BW - Lookup Total BW
      10.      
      11.           Prints Charge (calculation) = (Net Prints * .10)

           I setup 1 relationship being Client ID between the two tables. 

           The problem is that for the the 2nd record i get nothing for lookup field. 

           Any thought? 

        • 1. Re: Finding the difference of the field between current record and previous one
          philmodjunk

               How does Current Balance Prints get a value? If this is a number field, how does that number get entered into that field. If it is a summary or calculation field, how have you set that up to work?

               It just seems to me like you should have a number field somewhere where this value is entered as the amount on the current invoice or order instead of subtracting the previous record's cumulative total from the current record's cumulative total.

          • 2. Re: Finding the difference of the field between current record and previous one
            JohnAbenaim

                 right now I manually input it. But i want to be able to import it from an excel report later on. 

                  

            • 3. Re: Finding the difference of the field between current record and previous one
              philmodjunk

                   But don't you have a simple number field where you enter the value that you are trying to calculate here by subtracting? Why do you only have cumulative values to work with?

                   Where does my mental model of what you are trying to do go wrong?

                   Transaction 1 10 prints   Cum total: 10
                   Transaction 2  20 prints  Cum total: 30
                   Transaction3  35 prints   Cum Total: 65

                   It seems like you want to calculate 20 for Transaction 2 by subtracting 10 from 30, but don't you already have 20 entered as a number somewhere in your system? Why the need to calculate it from the cumulative totals in Transaction 1 and Transaction 2?

                   PS. and what I show here, I understand to be 3 records, one for each transaction.

              • 4. Re: Finding the difference of the field between current record and previous one
                JohnAbenaim

                     No the thing is that my excel report only gives me the cumulative total printed per Client and that is the reason why every month i have to find out how many prints i need to charge each person. 

                      

                      

                • 5. Re: Finding the difference of the field between current record and previous one
                  philmodjunk

                       And why is that? Sorry but that really has me scratching my head here as it seems to unnecessarily complicate your system. Do you have any control over how the data is recorded in the spread sheet?

                       Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; Balance of BW - GetNthRecord (Balance of BW ; N - 1 )  ; "" ) )

                       looks correct to me provided that all the records where you need this calculation are in the current found set with correct sorting and only for a specific customer (not multiple customers at the same time.)

                  • 6. Re: Finding the difference of the field between current record and previous one
                    JohnAbenaim

                         No unfortunately that is how the printer report come out. It just give a total prints from the date a user is entered in. 

                         So every month when i pull the report it a total for B&W prints, Total for Color prints and Total Fax sent for each user. 

                         My final goal is to import every month the excel report so that each client gets updated and added if imported for the 1st time. From there, i want to create a monthly report that will give me an exact charge per client for Prints and Fax sent if any activity since last record. 

                         It would look like: 

                         Client Name | $ Prints  | $ Fax

                         Client 1         | $10        | $10

                         Client 2

                         Client 3

                          

                         Not sure how complicate it will be.

                    • 7. Re: Finding the difference of the field between current record and previous one
                      philmodjunk

                           To repeat:

                           Let ( N = Get ( RecordNumber ) ; If ( N > 1 ; Balance of BW - GetNthRecord (Balance of BW ; N - 1 )  ; "" ) )

                           looks correct to me provided that all the records where you need this calculation are in the current found set with correct sorting and only for a specific customer (not multiple customers at the same time.)

                           And this should not be defined in a stored calculation field nor a number field with an auto-entered calculation if you want this field to update with each different found set in which you pull up this data.

                           But if you plan on importing this data and it will not change once imported, you could use this expression in an auto-enter calculation provided that you enable auto-enter options during the import.

                      • 8. Re: Finding the difference of the field between current record and previous one
                        JohnAbenaim

                             What do you mean by "not multiples customers at the same time" 

                             How do you think i should setup to get my monthly report showing the list of Clients with charges?

                        • 9. Re: Finding the difference of the field between current record and previous one
                          philmodjunk
                               

                                    What do you mean by "not multiples customers at the same time"

                               I may be making an incorrect assumption about what you are trying to do, but the GetNthRecord function copies data from the preceding record no matter if that record is for the same client or a different client. If you have a found set of records for more than one client up on your screen, then this calculation could copy data from a record for Client A to subtract from data from a record for Client B.

                               

                                    How do you think i should setup to get my monthly report showing the list of Clients with charges?

                               I would exhaust every available option to get the data being imported to be in a more sensible format. wink

                               But let's focus on getting the calculation that you have to work before looking at how to set up a report where this works for multiple clients--which may not take much of a change to handle correctly.

                               How are you implementing the calculation that you posted? Is this used in a calculation field or as an auto-enter calculation for a number field? If this is a calculation field, is it a stored calculation field or unstored?

                               And the key question: is this data ever subject to editing after you import it? Do you ever need this expression to recalculate once you have imported the data and it has done the needed subtractions?

                          • 10. Re: Finding the difference of the field between current record and previous one
                            JohnAbenaim

                                 You are right lets focus on getting the database to work first. 

                                 It is used as a calculation field and it is unstored. 

                                 I will import the data once a month to get my monthly report of charge, so i don't think it needs to recalculate anything, it just has to keep giving me the Net printed after each import. 

                                  

                            • 11. Re: Finding the difference of the field between current record and previous one
                              JohnAbenaim

                                   Phil, 

                                   What do you think if i start my project from the "Invoice Starter Solution" of Filemaker and i then add the field to get me the NET quantity after monthly import from Excel?

                                   What do you think?

                              • 12. Re: Finding the difference of the field between current record and previous one
                                philmodjunk

                                     It's hard to say without knowing a lot more about your database.

                                     From here, I have yet to spot why your GetNthRecord calculation is failing on the second record.

                                     What does your data look like in the source file that you are importing?

                                     Does the total increase for each row even if the preceding row was for a different client or does it start over with each new client record? Is there more than one record in sequence for a given client for that matter?

                                     This is needed to determine if the calculation needs to be modified to not access the previous record if it is for a different client.

                                     What I am considering here, is that you set up this calculation on a number field as an auto-enter calculation and enable auto-enter options during each import. That should produce an identical result to your unstored calculation in most circumstances but avoids some known issues with this type of getNthRecord "chain" of calculations.

                                     Known issues with GetNthRecord in an unstored calculation:

                                     Change the sort order, change the found set and you get different values. Sometimes that's what you want, but not always.

                                     Large found sets can be very, very slow to update.

                                     Large found sets can result in this calculation showing a ? for lower records in the found set due to recursion maxing out FileMaker's ability to handle recursive calculations. (The exact point at which you see this will depend on whether you are using FileMaker Pro or FileMaker Go.)

                                • 13. Re: Finding the difference of the field between current record and previous one
                                  JohnAbenaim

                                       I have attached a sample of the data that i want to import. 

                                       The list of account name may change from month to month. We might add new one or just delete the ones that are no longer clients. 

                                       The weird thing about GetNthRecord calculation is that it looks like it works for the B&W Lookup field but not for the others one. 

                                  At this point i am only trying to do it with one Clients without importing anything but just buy inputing the Balances Field. 

                                  1 2 3 Previous Next