11 Replies Latest reply on Nov 11, 2011 9:58 AM by philmodjunk

    searching with multiple dates



      searching with multiple dates


       I have a application to 'hire' out stock, and record when it is returned. Written within the 'invoice lines' table I have a layout that records the quantity & date of stock that has been returned. for example within each Invoice Line table there are fields 'Return_Date_01' up to 10 and 'Qty_Returned_01' again upto 10.  I would now like to search all the 'Return_Date_01 - 02 etc, for product returned on a specific date. I show a grab from my layout, detailing the typical format.

      I also attach the script I have, but it does not return any relevant values. I think I am looking for an 'or' function (possibly), to match any of the return dates to the globally held request date.





        • 1. Re: searching with multiple dates

          A table of related records instead of 10 different date fields would make the search much easier to implement. Your scripted search could then use a single Set Field instead of 10 and you can have more or less return dates as needed without having to make design changes to your database to accomodate it.

          With the current structure, you can get what you want if you ad a New Record/Request step in between each set field step in your script. That puts the date criteria for each date field in a separate request and this will produce a find with your specified "OR" logic.

          • 2. Re: searching with multiple dates

             Hi, Thank you for the direction. I have created the relevant table and associated fields, only 5 in total 'return date', 'return qty' etc.  I am populating these new 'return fields' with a script as the layout for the data entry belongs to another table. The structure works well. My question now relates to the viewing of the various records. I am having difficulty in viewing related fields from the other tables, for example customer name, & product description. In the relationship graph I have linked the 'kf_' fields to the Invoice_Lines, Invoice and Products tables. I would like to search the 'Returns' table and display all invoice line items have been returned on a spcific date. Listing the showing the relevant invoice and return details.

            Regards  Marty


            • 3. Re: searching with multiple dates

               Here is the script..

              • 4. Re: searching with multiple dates

                I see two possible problems, the first is that there is no key field in GRN_Returns to link it to the Invoice_lines table.

                The second is that last Set Field line is setting "Return_comment" to "x_scrap_qty"

                • 5. Re: searching with multiple dates

                  Once those issues are resolved, a portal to this table can display the records and will look much like your original set of multiple date fields.

                  • 6. Re: searching with multiple dates

                     I have table relations as shown, should this now allow the correct data to be shown?

                    Do I actually need a portal to view the fields from tables, @Invoice_Customer', 'Invoice_Lines' and the various returns attributed to that particular invoice line in 'GRN_Returns' ?

                    I have a layout owned be GRN_Returns, I can only see all the GRN data but no Line information....

                    • 7. Re: searching with multiple dates

                      In most situations, you'd use a layout based on Line_Cust_Invoice with a portal to GRN_Returns to list the return dates. That would seem a closer fit to your original screen shot.

                      • 8. Re: searching with multiple dates

                         Hi, I have tried to emulate your comments, I think they are the solution I seek. I have good data capture of the returned products, and can see the data in a separate layout. When creating a portal in the 'lines_cust_inv' layout I can not seem to see any data. I wish to see all the return details for a specific line on the invoice.

                        Does the screen grabs show where the error could be?


                        • 9. Re: searching with multiple dates

                          What data do you have in _kf_cust_lines_id?

                          From what you show, it's possible that this field is either empty or contains a value that, for some reason, does not match the value in _kp_cust_lines_id in line_Cust_invoice.

                          • 10. Re: searching with multiple dates

                            Ok, under scrutiny you are correct it is empty !

                            Possibly a basic question; How does it get populated or as you mentioned get populated with un-matching data ? Apologies if this is basic concepts, but I was under the impression that if they were linked in the relationship tables the primary key would populate the foreign key fields, or do I have to cover other consideration ?


                            • 11. Re: searching with multiple dates

                              It depends on how the records in the related table are created.

                              If, in Manage | database | relationships, you enable "allow creation of records via this relationship" for GRN_Returns. You can now create new records in your portal to GRN_Returns simply by entering data into a blank row of the portal. FileMaker will automatically enter the matching value from _kp_cust_lines_id into _kf_cust_lines_id of this newly created record.

                              If you use other means to create the record in the portal such as with a script or by creating records on the GRN_Returns layout, you must also add a method for selecting the record in Lines_cust_Invoice to which it should be linked. A drop down list or pop up menu is one method for this. There are also scripted methods that capture the _kp_cust_lines_id in a variable so that the next new GRN_Returns record auto-enters this ID to automatically link it to that record.