1 2 Previous Next 17 Replies Latest reply on Jun 9, 2014 8:51 AM by philmodjunk

    Reconciliation Database

    KM

      Title

      Reconciliation Database

      Post

           I am trying to create a database where I can reconcile fuel use.  I currently have 2 tables which are Fuel use (records all fuel used- diesel and gas) and delivery (records all deliveries of diesel and gas)....

            

           I would like to create a report which shows the reconciliation of it... so it would add all the diesel gallons delivered, and subtract out all diesel used...

            

           Any help on where to go from here???

        • 1. Re: Reconciliation Database
          philmodjunk

               That will be a bit of a challenge given the fact that you use two different tables.

               I'd set up a "fuel transactions" ledger where the same table would log both deliveries and consumption much like you'd log deposits and withdrawals for a bank account.

               The fields defined for such a table might be:

               TransActionDate (Date)
               FuelType (text)
               FuelRecieved (Number)
               FuelConsumed (Number)
               cBal (calculation: FuelRecieved - FuleConsumed )
               sBalance ( Summary: Total of cBal )
               sBalanceRunning ( Summary: Total of cBal, Running total, restart totals with each sorted group (FuelType) )

               With these fields in place, a list view layout can list all transactions, sorted first by FuelType, then by TransactionDate. By including all but cBal and sBalance in the body of the layout, you'll get a "ledger like" view that not only shows your predicted current quantities for each fuel type, but how your stocks have risen and fallen over time--which can help you fine tune your re-order points for ordering more fuel of each type.

               You can also put sub summary layout parts "when sorted by FuelType" and put sBalance in that layout part to show the sub totals for each fuel type. If you add those sub summary parts and remove the Body layout part, you'll get one row for each fuel type and the total on hand.

               But for your reconciliation, You could modify that last approach slightly and add a Calculation field cTransType: If ( FuelConsumed ; "Consumed" ; "Received" ) and then you can add sub summary layout parts "when sorted by cTransType" to get sub totals for each fuel type for each type of transaction to get:

               Fuel Type: Deisel
                  Consumed: XXX
                  Received: YYY
               Balance: ZZZ

               Fuel Type: Gasoline
                  Consumed: XXX
                  Received: YYY
               Balance: ZZZ

               Every row in this example is a sub summary layout part (no body layout part) using either cTransType or FuelType as the "sorted by" field.

               PS. Given that you have a working system with two separate tables, you might set this up as a third table and use Import Records to periodically combine data from the current two tables into this single table for reporting purposes.

          • 2. Re: Reconciliation Database
            KM

                 Thank you so much!!! I went ahead and just used 1 table and I love the ledger layout!! Although I am not completely understanding the reconciliation portion though... Do I need to create summary fields for fuel consumption/received??

            • 3. Re: Reconciliation Database
              philmodjunk

                   By putting the same summary field inside different sub summary layout parts that specify different "when sorted by" fields, you can use the same summary field to display different sub totals. So the last version uses the same summary field, but uses different grouping (the type of transaction) to get sub totals for quantities received and quantities consumed from the same summary field.

              • 4. Re: Reconciliation Database
                KM

                     Could you possibly help me figure this one out?

                      

                     In each entry I have a field labeled "odometer" so every time fuel is consumed it tracks the odometer reading.  I would like to track the miles per gallon every time fuel is used based off the current odometer reading and the prior odometer reading as well as an overall average based mpg. 

                     I have tried using If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( Odometer ; Get ( RecordNumber ) + 1 ) ; Odometer ) - Odometer  but it isn't calculating correctly...I have this calculated field in the body and a subtotal by equip id

                • 5. Re: Reconciliation Database
                  philmodjunk
                       

                            In each entry I have a field labeled "odometer" so every time fuel is consumed it tracks the odometer reading.  I would like to track the miles per gallon every time fuel is used based off the current odometer reading and the prior odometer reading as well as an overall average based mpg.

                       Do you have a field that uniquely identifies the vehicle that is consuming the fuel logged in that entry?

                       GetNthNumber can be made to work, but it has significant limitations so it often isn't the best option for accessing data from another record in your table. It accesses data via its position in the current found set and the value you need may not always be consistently located in the same place in that found set. A self join relationship, on the other hand, can match to the specific record for the same vehicle with a date immediately preceding the current record's date and thus consistently access mileage from the correct record regardless of whether or not that record is even in the current found set.

                  • 6. Re: Reconciliation Database
                    KM

                         Yes, each data entry records the vehicle, and how much fuel was used, as well at the odometer reading..

                    • 7. Re: Reconciliation Database
                      philmodjunk

                           And presumably, the date of this transaction as well.

                           Let's say your Fuel Log table has these fields:

                           VehicleID
                           TransDate
                           Consumed
                           Delivered
                           Odometer
                           PreviousOdometer
                           MPG

                           I will assume that records that record a fuel delivery will not specify a vehicle ID.

                           You can set up a self join relationship that only matches to other transaction logs with the same VehicleID that have an earlier transaction date.

                           FuelLog::VehicleID = FuelLog|Vehicle::VehicleID AND
                           FuelLag::TransDate > FuelLog|Vehicle::TransDate

                           Where FuelLog|Vehicle is a second Tutorial: What are Table Occurrences? of FuelLog.

                           You can double click the relationship line linking these two occurrences and specify a sort order for FuelLog|Vehicle that sorts the related records by TransDate in descending order. This makes the immediately previous transaction log record the "First" related record.

                           You can then set up PreviousOdometer to auto-enter FuelLog|Vehicle::Odometer

                           and MPG can be defined as:

                           (Odometer - PreviousOdometer) / Consumed

                      • 8. Re: Reconciliation Database
                        KM

                             Is there a way to calculate the last record - 1st record?

                             I am creating a water meter database and have set it up similar to the fuel database. I created a second table occurrence of MeterReading called MeterReading2 and the relationships are set up

                             MeterReading::Location# = MeterReading2::Location#

                             MeterReading::Date > MeterReading2::Date

                              and MeterReading2 sorts Date in descending order....

                             Then I created a PreviousAcreFt to auto-enter MeterReading2::TotalAcreFt

                             My fields are:

                             Date

                             Location#

                             Time

                             Total Acre Ft

                             Previous Acre Ft

                              

                             My problem I am having is that my Previous Acre Ft field is empty, there is no data that is being auto-entered....and I am not sure that this is the best way to complete what I need to do...

                              

                        • 9. Re: Reconciliation Database
                          philmodjunk

                               When a calculation refers to a field from a related table via an unsorted relationship (the default option for relationships). it refers to the first related record.

                               The Last function can be used to reference data in the most recently created related record.

                               Last ( MeterReadings::Value ) - MeterReadings::Value

                               if evaluated from the context of a customer table, (Customer---<MeterReadings), will compute the difference of the most recent reading and the earliest reading. There are also ways to include dates in the match fields of the relationship in order to access some "first reading" other than the very first such related record--such as the first reading of the billing cycle, year, month, quarter...

                          • 10. Re: Reconciliation Database
                            KM

                                 The result of Last (MeterReading::TotalAcreFt) - MeterReading::TotalAcreFt is resulting in 0??

                            • 11. Re: Reconciliation Database
                              philmodjunk

                                   In what table did you define this? How is that table related to MeterReading?

                                   What context is specified for this calculation?

                                   Is it a calculation field? (It should be this) or a number field with an auto-entered calculation? (Should not be this)

                              • 12. Re: Reconciliation Database
                                KM

                                     It is a calculation field in the MeterReading table...

                                • 13. Re: Reconciliation Database
                                  philmodjunk

                                       It cannot be defined in that table. It must be defined in a related table that links to the meterReading table. Please note that I said:

                                       

                                            if evaluated from the context of a customer table, (Customer---<MeterReadings), will compute the difference

                                       But there is another option that might be set up in the meterReading table:

                                       assuming that all the records currently in your found set are for the same meter and that the meter always shows the same or a larger reading each time a new reading is logged, A pair of summary fields could be used to compute the difference. A "Maximum of" summary field and a "Minimum of" summary field could be used in this calculation:

                                       sMaxReading - sMinReading

                                       Unlike the first example, this is solely based on what records are in your found set. And it is possible to use GetSummary with these two summary fields to compute multiple first and last meter reading values for a summary report listing records from multiple Meters.

                                  • 14. Re: Reconciliation Database
                                    KM

                                         Thank you for your help!!

                                         I created another table named Location# and have an ActualAcreFt field with a calculation of the "Last" calculation and it works for my 1st location.  I have the ActualAcreFt in a subsummary field by Location# But all of the location's after are all pulling its last reading - the 1st date from the 1st location.

                                         My tables are set up like this:

                                         MeterReading------<Location#

                                          

                                    1 2 Previous Next