7 Replies Latest reply on Feb 17, 2017 8:34 AM by philmodjunk

    Return a list based on a criteria

    syms

      Hello everyone,

       

      In the solution I'm working on, I have a set of components or parts that are built by different suppliers. And I use those components or parts to build a kit. Until now everything is fine.

       

      My question is simple, how do I return (or calculate) a list of components based on their respective supplier?

       

      For example if I have Part A and Part B made by Supplier AB, Part C and Part D made by Supplier CD and Part E made by Supplier EE, I would like to return a list of Parts organized by their suppliers. In this example, as we have 3 suppliers, we will then have 3 lists with Part A and Part B (Supplier AB), Part C and Part D (Supplier CD) and Part E (Supplier EE).

       

      Thank you

        • 1. Re: Return a list based on a criteria
          Johan Hedman

          You can always do a  list and do Sub Summary of the list based on Supplier.

          • 2. Re: Return a list based on a criteria
            syms

            Thank you for your answer.

             

            My goal in fact is to obtain a different result based on Supplier. For example if I have Part A and Part B made by Supplier AB, Part C and Part D made by Supplier CD and Part E made by Supplier EE, I would like to obtain 3 forms containing Parts A and B for Supplier AB, Parts C and D for Supplier CD and Part E for Supplier EE. My guess is that a complex technique like this would require a script but I don't have much experience writing one that would give me this result. Does anybody have an idea or could explain me how to get to this result?

            • 3. Re: Return a list based on a criteria
              user19752

              How do you want to show the resulted "list" ?

              Seeing "calculate a list", it seems something like

              List(relatedTable::PartName)

              or

              ExecuteSQL ( SELECT PartName FROM someTable WHERE Supplier=?" ; "" ; "" ; Supplier )

              but it is hard to explain without your current database definition.

               

              Or, do you mean "obtain 3 forms" as print out 3 sheets, one for each supplier?

              • 4. Re: Return a list based on a criteria
                philmodjunk

                As Johan already suggested, a summary report can list your parts, each sub summary serving as a "header for the parts for a given supplier. Each can be the start of a new page.

                 

                Or you can set up a script that performs a find for each supplier to produce completely separate document for each supplier if you wish.

                • 5. Re: Return a list based on a criteria
                  syms

                  Let's say that I use Parts A, B, C, D and E to build a car. In the customer invoice, I can easily list the used parts. But then instead of creating an order invoice for each supplier that would sell me those parts, I would like to a list of a supplier given parts with each order invoice.

                   

                  The ExecuteSQL method you're referring to seems like what I am looking for. Could you tel me more about it or give me an example of how I could implement it? My goal by this method as you point it out is to obtain 3 sheets, one for each supplier containing the parts that need to be ordered

                  • 6. Re: Return a list based on a criteria
                    syms

                    I'm not sure that I follow but could you explain me how to do that? I would like to explore different ways to see whichone gives me the result I'm looking for

                    • 7. Re: Return a list based on a criteria
                      philmodjunk

                      To do that, I'd have to guess as to what data model you have put together. I'll do that, but my guess could be far different from what you actually have in place.

                       

                      Typical invoicing for parts used to make a repair is this basic set of relationships

                       

                      Invoice (or WorkOrder) -----<PartsNeeded (lineItems) >------Parts    (---< means "one to many" )

                      Since we also need to know suppliers, add one more table occurrence to get:

                       

                      Invoice (or WorkOrder) -----<PartsNeeded (lineItems) >------Parts >----Suppliers

                       

                      With that data model, you can use either a scripted find or Go To Related Records from the Invoice layout to pull up a Found set of PartsNeeded on a list view layout based on PartsNeeded followed by sorting the records by either the supplier name or supplier ID.

                       

                      Add a sub summary layout part "When sorted by Parts::SupplierID  (or when sorted by Suppliers::SupplierName). There's an option to specify a page break before every 1 occurrence if you want each on it's own page.

                       

                      You put the supplier info from Suppliers into the sub summary part and list the parts info in the body part. Note that subsummary parts are invisible if their "sorted by" field is not part of the current sort order.