8 Replies Latest reply on Feb 17, 2014 9:24 AM by philmodjunk

    concurrent line item help

    ericjlindholm

      Title

      concurrent line item help

      Post

           I want to be able to display a sum of the product quantity on line items all of the line items with the same Inventory ID number that are other invoices that have overlapping start and end dates(my line items are rentals so invoices have a start and end date).  I created table called Line Items_All and related Line Item::inventory ID=Line Item_All::inventory ID.  In a portal of displaying  line items:ALL on a layout displaying line items, This limits portal records to every time this item was on an invoice. 

           If i filter the portal with [ items will be visible when:  

      Line Items_All::ID ≠ Line Items::ID and 

      Line Item_ALL_invoice::Start Date    ≤    invoice::End Date and 

           Line Item_ALL_invoice::End Date     ≥   invoice::Start Date]
            
           is displays perfectly.
            
           I am trying to create a calculation Line Item::cNow Invoiced that would display Line Items_ALL::Quantity if it falls in the same date range and the invoice I am currently view.  Then I would create a sum field to total those to get a total invoiced to compare to qty on hand from inventory.  
            
           I cannot seem to get the calculation field to work.  if i use the about boolean statement, it returns 0 

        • 1. Re: concurrent line item help
          philmodjunk

          Line Items_All::ID ≠ Line Items::ID and 

          Line Item_ALL_invoice::Start Date    ≤    invoice::End Date and 

               Line Item_ALL_invoice::End Date     ≥   invoice::Start Date

               Needs to be a relationship instead of a portal filter expression. Calculations that refer to data in related tables are not affected by portal filters.

          • 2. Re: concurrent line item help
            ericjlindholm

                 WOW. this is making way more sense.  I can see how this will work but I cannot seem to figure out how to limit this to line items that have the same inventory ID.  the only reason i have Line Items_All::ID ≠ Line Items::ID was because i didn't want the item to show itself in the list.  so somewhere i think i need Line Items_All::ID =Line Items::ID to make it show only line items with the same inventory ID

            • 3. Re: concurrent line item help
              ericjlindholm

                   Hmm.. because i don't have a date field on my line items, i don't think i can make this work.  now that i know the proper place for my relationships, let me be more clear than my first post.  I need a table occurrence of line items that lists other invoices that have line items with matching inventory IDs whose invoice start and invoice end date overlap.

              • 4. Re: concurrent line item help
                philmodjunk

                     Sounds like you need to add those date fields. They can be set to auto-enter a date from the invoice parent record.

                • 5. Re: concurrent line item help
                  ericjlindholm

                       i tried a calculation but it doesn't seem to work.  Would you recommend a script that is triggered off the invoice date field to update the line items if it changes?  or is there a more efficient way?  Thank you. 

                  • 6. Re: concurrent line item help
                    philmodjunk

                         I did not suggest a a calculation per se as a Looked up Value setting does the same thing. And existing records will not automatically update when you add/modify an auto-entered calculation. See this thread for how to get them to update existing records: Updating values in auto-enter calc fields without using Replace Field Contents

                    • 7. Re: concurrent line item help
                      ericjlindholm

                           i got this working perfectly thanks to phil.  i can't tell you how much help you and the community are to me.  thank you. 

                           Now that I have this filter via table relationship going, i am using it to stream line my searches.  my only question so far is this.  is there a way to include records if a related global search field is empty.  for example, i have a client table.  I want to search by client type, status, account manager and name match.  i can do all of this through a portal by filtering values but then i need a million combinations of statements to make it values display if any of the search fields are empty.  is there a table relationship i can include to help with that?

                      • 8. Re: concurrent line item help
                        philmodjunk

                             Are you sure that a portal instead of a scripted find is really a better way to go? I do use search portals in my solution, but sometimes the complex criteria options that you need to specify are much simpler to set up as a scripted find.

                             As you have found, empty fields don't match to anything unless you are using the X operator which is not what you want here anyway. You'd have to come up with a calculation that copied the values of your global field, but used some other value when the field is empty.