1 2 Previous Next 21 Replies Latest reply on Mar 25, 2015 10:55 AM by stanmcman

    Easiest Way To Compare Two Portal Lists

    stanmcman

      FMPA13, FMS13, WIN8

       

      I have the following TOs:

      • Trips
      • Miles Line Items
      • Fuel Line Items
      • States

      Trips and States are joined by the Miles/Fuel Line Items. This is very similar in nature to Invoices > Line Items < Products, with the twist that I have 2 separate sets of line items. It works GREAT. But... here's my situation: This solution is designed to track the miles traveled in each state and the fuel purchased in each state. I need to ensure that the user doesn't enter fuel in a state for which he has no miles (usually done through clerical mistype).


      I have 2 portals in Trips: one for Miles and one for Fuel.

       

      What is the best way to check for this? Ideally it would be best if it could happen as fuel line items were added, since we enter all the miles first.

       

      Thanks,
      Stan

        • 1. Re: Easiest Way To Compare Two Portal Lists
          Mike_Mitchell

          This might be a dumb question, but why not use a single Line Items table that includes both Miles and Fuel? Call it something like Fill Ups?

          • 2. Re: Easiest Way To Compare Two Portal Lists
            siplus

            on exiting the state field in fuel line items you check for PatternCount(stateField) in ExecuteSQL("Select State From MilesLineItems where TripID = ?";"";"";FuelLineItems::fk_TripID) and revert record with a beep on 0.

            • 3. Re: Easiest Way To Compare Two Portal Lists
              siplus

              because if he fills up 3 times in Texas you will have

               

              048 TX 50

              048 TX 50

              048 TX 25


              (TripID, state, gallons)

              • 4. Re: Easiest Way To Compare Two Portal Lists
                stanmcman@yahoo.com

                Not a dumb question. I had gone with that option, but because of how we do data entry, it wasn't as conducive an option. It is INCREDIBLY easier to enter all miles first and then enter any fuel. Miles come from a trip report turned in by drivers. Fuel comes from either a stack of fuel receipts or a fuel report. This is used to keep track of Quarterly Fuel Taxes for trucking companies. We are entering data for hundreds of trucks for dozens of companies.

                 

                I was having trouble trying to make it work with combined miles and fuel together, which is why I broke it out into two. It works extremely well. I just figured there would/could be a fairly simple/straight forward way to "compare" the fuel line items to the miles line items for a selected trip, and make sure that every state with fuel also had miles.

                • 5. Re: Easiest Way To Compare Two Portal Lists
                  Mike_Mitchell

                  So you don't have a one-for-one correspondence between fuel and miles?

                  • 6. Re: Easiest Way To Compare Two Portal Lists
                    Mike_Mitchell

                    No, he'd have:

                     

                    048 TX 50 310

                    048 TX 50 312

                    048 TX 25 155

                     

                    (TripID, state, gallons, miles)

                     

                    And ... so what? Missing why this is a problem.

                    • 7. Re: Easiest Way To Compare Two Portal Lists
                      siplus

                      you can't avoid having to sum the gallons but at least you can avoid having to sum the miles. (only 1 entry, 048 TX 777). Also, you can handle the miles line items to the maintenance dept who will change tires or brakes or whatever based upon miles done and handle the fuel line items to the accounting dept for different reasons. Less not needed info going around.

                      • 8. Re: Easiest Way To Compare Two Portal Lists
                        Mike_Mitchell

                        Hm. Okay, so the primary entity is the "trip". I have a similar solution, but it's intended for consumer use. It's very easy to write down the odometer and get a receipt at each fill-up. You put in the date, odometer, cost and gallons, and the solution calculates cost / gal, MPG, maintenance schedule, etc. In that case, you're really not concerned about a "trip", but I can see why you would be for a trucking company. So my paradigm is a little different.

                        • 9. Re: Easiest Way To Compare Two Portal Lists
                          stanmcman

                          Mike, If I understand your question correctly, then that is correct. While every state we travel in will have miles, they won't all have fuel. For instance, If I a trucker took a load from Grand Rapids, MI to Miami FL, it would look something like this:

                           

                          Miles Line Items: (State, Miles)

                          MI 127

                          IN 283

                          KY 138

                          TN 171

                          GA 363

                          FL 421

                          Total = 1503

                           

                          Fuel Line Items (State, Gallons)

                          KY 89.7

                          GA 78.4

                          FL 59.1

                          Total = 227.2

                          • 10. Re: Easiest Way To Compare Two Portal Lists
                            stanmcman

                            Yes, in the scenario I'm laying out, the Trips is the primary. It is comparative to Invoices, Line Items, Products where:

                            • Trips  = Invoices
                            • Line Items = Miles/Fuel Line Items
                            • States = Products
                            • 11. Re: Easiest Way To Compare Two Portal Lists
                              Mike_Mitchell

                              Yeah, took me a minute to catch up.  

                               

                              siplus has given you a good option using ExecuteSQL ( ). Assuming the state code is available in both Line Item tables (based on your description, I think it is), you could also do something like this:

                               

                                   not IsEmpty ( FilterValues ( List ( FuelLineItems::State ) ; List ( MilesLineItems::State )))

                               

                              Use the same Script Trigger siplus recommends (OnRecordCommit) to check the calculation.

                               

                              HTH

                               

                              Mike

                              • 12. Re: Easiest Way To Compare Two Portal Lists
                                siplus

                                Well Mike, yes, the trip is what I intended as primary entity, too. During the trip we can have 2 different kinds of events:

                                 

                                1) Crossing an interstate border (which triggers an event in the Miles line items, registering the current mile reading in the current record as end value, creating a new record and setting it as start value), or

                                 

                                2) Fueling, which creates a new record in the Fuel line items, registering quantity and price paid.

                                 

                                If the purpose were to cross the US and give an average value of the $$ paid per gallon depending on the state, then a single line item table could probably be better. But as I understood the OP's problem, that's not the case.

                                • 13. Re: Easiest Way To Compare Two Portal Lists
                                  Mike_Mitchell

                                  I got it. Took me a bit, but I got it.  

                                  • 14. Re: Easiest Way To Compare Two Portal Lists
                                    justinc

                                    I will make a minor suggestion to Siplus' recommendation:  you can do it with a single ESQL statement, instead of using PatterCount() combined with an ESQL call.  Here's what I would suggest:

                                     

                                    ExecuteSQL("Select COUNT ( * ) From MilesLineItems where TripID = ? AND StateID =? ";"";"";FuelLineItems::fk_TripID ; Statefield )
                                    

                                     

                                    This returns how many MilesLineItems there are for a Trip in a given state.

                                     

                                    Now for a large departure from Sipuls' solution. 

                                     

                                    What about some different UI instead?  Not sure where you are starting from UI wise, but I will assume that you are on a 'Trip' record, probably a detail view, with a portal to 'Miles' and a portal to 'Fuel' tables.  I would leave the portal to 'Fuel' on the layout but only as a non-editable list view.  Then, what if you put an 'Add Fuel' popover button in the 'Miles' portal?  This popover would then show a small window where you would enter in the Fuel information.  You could save this information in temporary global fields, and then when they close the popover, have a script trigger that would save that temporary info into a new record in 'Fuel' table.  You would grab the StateID from the 'Miles' record that was clicked on.

                                     

                                    --  J

                                    1 2 Previous Next