8 Replies Latest reply on Aug 26, 2010 2:28 PM by Marty1

    Searching on searches



      Searching on searches



      I have, through another script, performed a find routine which has, for example, returned 25 products.

      I now wish to perform an additional search/find on these 25 results against a user entered date field, X_date, and wish to see all the products that were purchased before that date.

      I have 2 databases, 'Products' (for the initial search criteria) and 'Customers' which holds the sold_date field.

      Please can you point me in the correct direction.


      Best regards




        • 1. Re: Searching on searches

          What relationship do you have between customers and products?

          Are you trying to find all Products purchased by a given customer prior to a specified date?

          • 2. Re: Searching on searches


            The product is initially found through 2 pull down menu's held in value lists, and using conditional statements.

            I tried to paste a screen grab of my relationship table, but could not - sorry.

            The products are related via their unique part numbers

            I am trying to search all [product x] that was purchased by any customer before a certain date.

            FYI - All products have reference to a {category} and then the product

            For example categories are 'poles', 'boxes' and 'lids', then boxes are split into 'cardboard', 'metal' and 'wood', so by selecting 'boxes' and 'wood' it lists all the wooden boxes. Then I want to see them by date that the customer purchased them.

            I hope that helps



            • 3. Re: Searching on searches

              The products are related via their unique part numbers.

              How? How do you match one customer to 5 different products that way?

              You can edit your first post to upload a screen shot and I think I need it before I answer your question. It seems like you should have several more related tables involved here.

              This sounds like an invoicing system and usually one has a Products table, a customer table, an invoice table and a LineItems table for such a system.


              • 4. Re: Searching on searches

                Yes you are correct, that is exactly how I have constructed the project. I have now edited the first page to have the attachment.

                • 5. Re: Searching on searches

                  So is it the information in lines_cust_Invoice that represents the "products"? Is the date a date recorded in Invoices_Customer for a Customer named in the Customers table?

                  Or are you trin gto do this from the Invoices_Customer table?

                  • 6. Re: Searching on searches

                    Yes, the information or products I am looking for are held in lines_cust_invoice.

                    The date is held in invoices_customers table.

                    I have a layout (looking at lines_cust_invoice, to which I can enter and find the product in question and also enter a {search} date, now I need a way to script search, I presume, all the invoice lines that have that particular product before the given date.


                    • 7. Re: Searching on searches

                      That's the right layout to search on for this. You can put the name field from customers and the date field from Invoice_Customer on this layout.

                      1. Enter find mode.
                      2. Enter the customer's name
                      3. Enter "< 8/25/2010" in the date field
                      4. Perform the find and you'll find all the Line Item records for items purchased by that customer prior to today's (Aug 25, 2010) date.


                      This can also be scripted.

                      • 8. Re: Searching on searches

                        Manyt thanks, I will follow your guidance, hopefully obtaining the solution.