3 Replies Latest reply on Jul 8, 2010 1:37 PM by philmodjunk

    Search multiple fields for one value and create a list



      Search multiple fields for one value and create a list


      I have an order form created for my advertising department.  (there is a lot more to it but this is where I'm stuck)  On this form: a single order can run multiple times, so there are 14 slots available to enter in "run dates" Now I need to make a "run sheet"  Ideally this run sheet will print a list that shows all of the orders (Company, ad size and cost) that will run for a particular date.   So.... I'm guessing a portal is used?  How do i make my database search the 14 "run date" slots to form one list?  I am new to FileMaker but have worked in Access quite a bit in the past. So while I understand databases, all the names I'm used to are different (and a lot of the functions) and can't for the life of me figure out how to search what I need to do. Please don't bind my question by the terms I've used, any solution, or the best possible way to do this is preferred. And even teach me some FileMaker Vocab if you please :)

        • 1. Re: Search multiple fields for one value and create a list

          Well what do you mean by "slot" ? You should have a portal ( think MS Access Subform ) to a line items table with 14 portal rows for your orders. You can search the records in this portal quite easily, but the devil is in the details.

          With a portal, you have a parent record such as an invoice record and a set of child records such as individual invoice line items. Do you want to find parent records that match criteria you enter into the portal row or all child records that match your criteria?

          Either option is possbile.

          • 2. Re: Search multiple fields for one value and create a list

            Sorry, slot=field, there are 14 date fields. As for everything you said... consider me lost. lol

            What I do have currently is 2 tables:

            A client table (listing strictly client information) and a

            Order table (sizes, prices, run dates, taxes... and so on) I think you could consider our order table an invoce table. I don't have experience in accounting so i don't know how loose that term is.

            We do not have a seperate table that catalogs all of our "products" becase there are too many variables. I THINK thats what you mean by individual line items table? In that I don't think I have child records? as each invoice serves as it's own item?

            But in a more broke down way:

            I have 14 date fields in my Order Table

            I need to, in a layout, search the 14 date fields for a single value (a single date) and produce a list of all the orders that have the date mentioned in one of those 14 fields.

            The list should show "Client name" "Size" and "Price" ((("Size", "Price", and all 14 Date fiels are in the Order Table, "Client name" is linked to it from the Client Table)))

            So, for example, I need to know what ads will run the July 22nd Edition. I type in that date and it lists for me the clent names, ad sizes and cost. (one copy goes to accounting to show how much income was generated that edition, and a copy goes to layout to show the sizes we are running) All of this information is pulled from the same table, save the customer name, which is linked to it via a serial number)

            Thank you so much I really appreciate your help Mr PhilModJunk ;)

            • 3. Re: Search multiple fields for one value and create a list

              Instead of 14 fields, you need an additional line items table and apparently an invoice table as well if your clients can order more than one item at a time.


              One client can place many orders in the invoices table and one invoice can list many items for that order.

              The typical approach for this is to use a portal to line items (in MS Access, this is called a subform) so that you can record each type of item purchased by a client in a given sales transaction. Information such as size and price are fields in this line item table.

              For an example file, examine the invoices starter solution that comes with filemaker 11. (Choose new from starter solution.)