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

    Searching on searches

    Marty1

      Title

      Searching on searches

      Post

      Hi,

      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

       

      Marty

      25-08-2010_23-18-57.jpg

        • 1. Re: Searching on searches
          philmodjunk

          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
            Marty1

            Hi,

            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

             

            Regards

            • 3. Re: Searching on searches
              philmodjunk

              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
                Marty1

                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
                  philmodjunk

                  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
                    Marty1

                    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
                      philmodjunk

                      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
                        Marty1

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

                        Regards

                         

                        Marty