Professional Web Applications Themes

Performing a FIND over multiple fields? - FileMaker

Is there anyway to perform a find on multiple fields? I'm keeping, at works, a list of all the used photographic equipment that we did purchased from customers. Sometimes, we need to know from who we did purchased this item, or when, for example a Tamron lens. My problem is : whenever a customer sells us stuff, he normally have many items : a body, one or two lenses, adapters, and so on. On each item, we list serial numbers, accessories, etc. So I've those are my fields : Department1 Brand1 Product1 Number1 Price1 Department2 Brand2 Product2 Number2 Price2 Department3 ...

  1. #1

    Default Performing a FIND over multiple fields?

    Is there anyway to perform a find on multiple fields?

    I'm keeping, at works, a list of all the used photographic equipment that we
    did purchased from customers.

    Sometimes, we need to know from who we did purchased this item, or when, for
    example a Tamron lens.

    My problem is : whenever a customer sells us stuff, he normally have many
    items : a body, one or two lenses, adapters, and so on. On each item, we
    list serial numbers, accessories, etc. So I've those are my fields :

    Department1 Brand1 Product1 Number1 Price1
    Department2 Brand2 Product2 Number2 Price2
    Department3 Brand3 Product3 Number3 Price3

    Etc. up to 10 fields...

    For example:

    Body Pentax ME Super 345678 150$
    Lens Pentax 50mm f/1.7 23345 50$
    Lens Tamron 70-210mm f/4,0-5,6 123423 80$

    And so on...

    So, whenever I'm performing a search (using serial number) over these
    fields, I've to put, for example, the serial number in a specific field
    (let's say field « Number1 ») and perform the find. No result. Okay.
    Let's try another field... « Number2 ». 2nd search, still no result.
    That's a pain in the butt...

    Any other way to perform a search over every field, using the serial number
    as the variable? =))

    Jean-François Landry Guest

  2. #2

    Default Re: Performing a FIND over multiple fields?

    I'm not clear about your structure. If you are buying back goods from customers
    that you want to track, you should have a Purchases/BuyBack database with a
    serial ID, then a second db for the items being purchased. That way there is
    only one (related) Item Number field.

    It sounds as if you mean by 'up to 10 fields' that you have 10 brand fields and
    10 number etc per each purchase event from a customer record? If so then this
    can be pd to a better structure:

    A record in Purchase/BuyBack is a purchase event, use A unique serial record ID
    and create a rel to the Items db... Individual items can be entered in a portal
    using the rel...

    If I have misunderstood, get back...

    Chris Brown
    Neurosurgery
    University of Adelaide







    Jean-François Landry wrote:
    > Is there anyway to perform a find on multiple fields?
    >
    > I'm keeping, at works, a list of all the used photographic equipment that we
    > did purchased from customers.
    >
    > Sometimes, we need to know from who we did purchased this item, or when, for
    > example a Tamron lens.
    >
    > My problem is : whenever a customer sells us stuff, he normally have many
    > items : a body, one or two lenses, adapters, and so on. On each item, we
    > list serial numbers, accessories, etc. So I've those are my fields :
    >
    > Department1 Brand1 Product1 Number1 Price1
    > Department2 Brand2 Product2 Number2 Price2
    > Department3 Brand3 Product3 Number3 Price3
    >
    > Etc. up to 10 fields...
    >
    > For example:
    >
    > Body Pentax ME Super 345678 150$
    > Lens Pentax 50mm f/1.7 23345 50$
    > Lens Tamron 70-210mm f/4,0-5,6 123423 80$
    >
    > And so on...
    >
    > So, whenever I'm performing a search (using serial number) over these
    > fields, I've to put, for example, the serial number in a specific field
    > (let's say field « Number1 ») and perform the find. No result. Okay.
    > Let's try another field... « Number2 ». 2nd search, still no result.
    > That's a pain in the butt...
    >
    > Any other way to perform a search over every field, using the serial number
    > as the variable? =))
    Chris Brown Guest

  3. #3

    Default Re: Performing a FIND over multiple fields?

    In article <BB6F193F.A679%cyliddsympatico.ca>,
    Jean-Fran=?ISO-8859-1?B?5w==?=ois Landry <cyliddsympatico.ca> wrote:
    >So, whenever I'm performing a search (using serial number) over these
    >fields, I've to put, for example, the serial number in a specific field
    >(let's say field « Number1 ») and perform the find. No result. Okay.
    >Let's try another field... « Number2 ». 2nd search, still no result.
    >That's a pain in the butt...
    >
    >Any other way to perform a search over every field, using the serial number
    >as the variable? =))
    I agree with Chris, that this is not really the way to get this job done.
    However, if this is the structure you have, then you might consider a
    global field and a script with multiple find requests.

    Make a global field (call it g_Find or something) that you place on a
    visible spot on your working layout. You enter the serial number you are
    hunting for in this field.

    Then make a script that goes something like this:

    Enter Find Mode (do *not* check "restore")
    Set Field (Number1, g_Find)
    New Record/Request
    Set Field (Number2, g_Find)
    New Record/Request
    Set Field (Number3, g_Find)
    New Record/Request
    Set Field (Number4, g_Find)
    New Record/Request
    Set Field (Number5, g_Find)
    New Record/Request
    Perform Find (do *not* check "restore")
    Set Field (g_Find, "")

    Add more SetField & New Record commands as needed. Then make a little
    button next to your global field on your layout labeled "go" or
    something. You enter a number in the global & click the button. A Find
    request for your number is run against every relevent field, displaying
    all records with the number in any of these fields. (The final script
    step is to clear out your global field for the next search.)

    There are other tweaks, such as error capturing & If statements that
    display a message if no records are found, etc. But this is the basic
    structure.

    I prefer setting up a script like this "manually," rather than running the
    find once, them memorizing the find requests in a script. Subsequent
    tweaking may need to be done, like adding or subtracting or editing find
    requests. Much easier to do this with a script like this than to rerun
    the find correctly & rememorize it.

    High on my wishlist is the ability to see and edit the memorized script
    functions: finds, sorts, import/export field mapping, page setups, etc.

    Steve Brown
    eyebrown@mindspring.com Guest

  4. #4

    Default Re: Performing a FIND over multiple fields?

    In reply to [email]eyebrown-2508030828050001sdn-ap-031tnnashp0175.dialsprint.net[/email]
    on 8/25/03 5:25 AM by [email]eyebrownmindspring.com[/email] [email]eyebrownmindspring.com[/email]:

    Or upgrade to version 6.x and use the New Find/Replace function. It allows a
    find in All Records or Current Record, and All Fields or Current Field. It's
    very similar to the find features in a text programs.

    HTH

    Lee
    > In article <BB6F193F.A679%cyliddsympatico.ca>,
    > Jean-Fran=?ISO-8859-1?B?5w==?=ois Landry <cyliddsympatico.ca> wrote:
    >
    >> So, whenever I'm performing a search (using serial number) over these
    >> fields, I've to put, for example, the serial number in a specific field
    >> (let's say field « Number1 ») and perform the find. No result. Okay.
    >> Let's try another field... « Number2 ». 2nd search, still no result.
    >> That's a pain in the butt...
    >>
    >> Any other way to perform a search over every field, using the serial number
    >> as the variable? =))
    >
    > I agree with Chris, that this is not really the way to get this job done.
    > However, if this is the structure you have, then you might consider a
    > global field and a script with multiple find requests.
    >
    > Make a global field (call it g_Find or something) that you place on a
    > visible spot on your working layout. You enter the serial number you are
    > hunting for in this field.
    >
    > Then make a script that goes something like this:
    >
    > Enter Find Mode (do *not* check "restore")
    > Set Field (Number1, g_Find)
    > New Record/Request
    > Set Field (Number2, g_Find)
    > New Record/Request
    > Set Field (Number3, g_Find)
    > New Record/Request
    > Set Field (Number4, g_Find)
    > New Record/Request
    > Set Field (Number5, g_Find)
    > New Record/Request
    > Perform Find (do *not* check "restore")
    > Set Field (g_Find, "")
    >
    > Add more SetField & New Record commands as needed. Then make a little
    > button next to your global field on your layout labeled "go" or
    > something. You enter a number in the global & click the button. A Find
    > request for your number is run against every relevent field, displaying
    > all records with the number in any of these fields. (The final script
    > step is to clear out your global field for the next search.)
    >
    > There are other tweaks, such as error capturing & If statements that
    > display a message if no records are found, etc. But this is the basic
    > structure.
    >
    > I prefer setting up a script like this "manually," rather than running the
    > find once, them memorizing the find requests in a script. Subsequent
    > tweaking may need to be done, like adding or subtracting or editing find
    > requests. Much easier to do this with a script like this than to rerun
    > the find correctly & rememorize it.
    >
    > High on my wishlist is the ability to see and edit the memorized script
    > functions: finds, sorts, import/export field mapping, page setups, etc.
    >
    > Steve Brown
    Lee Smith Guest

  5. #5

    Default Re: Performing a FIND over multiple fields?

    I agree with chris brown that you need to have more than one database,
    but it doesn't seem like one database should be based on serial
    numbers. I would in fact have three databases. One that stores
    customer information, such as name, address phone etc. You should
    have a field that assigns a unique number to each customer.

    Then a second database that lists each item in a separate record, like

    Department1 Brand1 Product1 Number1 Price1

    but for just ONE item.

    Then a third database that bridges these two--a transation database
    bringing the items together with the person who sold them. It would
    contain a transaction ID of its own, plus the customer ID plus the
    date.

    In the second database, you would need fields for the customer ID and
    the transation ID. then, when you look up a serial number, you can use
    two portals to look up the contact and the date of transation, and
    even show in the same screen the other items that were brought in with
    it.

    The reason to have three databases instead of two is that there is the
    possibility that the same customer might sell you stuff on two
    separate occations, and it would be prudent to keep track of that.

    Also, the reason that I would have customer ID numbers instead of
    looking up by names, is that if someone's name gets spelled
    incorrectly the first time, or if someone changes "David" to "Dave" it
    could mess up your relation. That's why it's always a good idea to
    relate databases using serial numbers that won't ever change.

    Hope this helps

    David Averbach
    UC Berkeley

    Jean-François Landry <cyliddsympatico.ca> wrote in message news:<BB6F193F.A679%cyliddsympatico.ca>...
    > Is there anyway to perform a find on multiple fields?
    >
    > I'm keeping, at works, a list of all the used photographic equipment that we
    > did purchased from customers.
    >
    > Sometimes, we need to know from who we did purchased this item, or when, for
    > example a Tamron lens.
    >
    > My problem is : whenever a customer sells us stuff, he normally have many
    > items : a body, one or two lenses, adapters, and so on. On each item, we
    > list serial numbers, accessories, etc. So I've those are my fields :
    >
    > Department1 Brand1 Product1 Number1 Price1
    > Department2 Brand2 Product2 Number2 Price2
    > Department3 Brand3 Product3 Number3 Price3
    >
    > Etc. up to 10 fields...
    >
    > For example:
    >
    > Body Pentax ME Super 345678 150$
    > Lens Pentax 50mm f/1.7 23345 50$
    > Lens Tamron 70-210mm f/4,0-5,6 123423 80$
    >
    > And so on...
    >
    > So, whenever I'm performing a search (using serial number) over these
    > fields, I've to put, for example, the serial number in a specific field
    > (let's say field « Number1 ») and perform the find. No result. Okay.
    > Let's try another field... « Number2 ». 2nd search, still no result.
    > That's a pain in the butt...
    >
    > Any other way to perform a search over every field, using the serial number
    > as the variable? =))
    David Averbach Guest

  6. #6

    Default Re: Performing a FIND over multiple fields?

    Hi David,

    thought that 3 may confuse the issue somewhat; I agree that in most situations 3 db's would be better
    design. My perspective was, the BuyBack Event is really the primary data, and the actual customer
    secondary (and rarely required...).

    regards

    Chris



    David Averbach wrote:
    > I agree with chris brown that you need to have more than one database,
    > but it doesn't seem like one database should be based on serial
    > numbers. I would in fact have three databases. One that stores
    > customer information, such as name, address phone etc. You should
    > have a field that assigns a unique number to each customer.
    >
    > Then a second database that lists each item in a separate record, like
    >
    > Department1 Brand1 Product1 Number1 Price1
    >
    > but for just ONE item.
    >
    > Then a third database that bridges these two--a transation database
    > bringing the items together with the person who sold them. It would
    > contain a transaction ID of its own, plus the customer ID plus the
    > date.
    >
    > In the second database, you would need fields for the customer ID and
    > the transation ID. then, when you look up a serial number, you can use
    > two portals to look up the contact and the date of transation, and
    > even show in the same screen the other items that were brought in with
    > it.
    >
    > The reason to have three databases instead of two is that there is the
    > possibility that the same customer might sell you stuff on two
    > separate occations, and it would be prudent to keep track of that.
    >
    > Also, the reason that I would have customer ID numbers instead of
    > looking up by names, is that if someone's name gets spelled
    > incorrectly the first time, or if someone changes "David" to "Dave" it
    > could mess up your relation. That's why it's always a good idea to
    > relate databases using serial numbers that won't ever change.
    >
    > Hope this helps
    >
    > David Averbach
    > UC Berkeley
    >
    > Jean-François Landry <cyliddsympatico.ca> wrote in message news:<BB6F193F.A679%cyliddsympatico.ca>...
    > > Is there anyway to perform a find on multiple fields?
    > >
    > > I'm keeping, at works, a list of all the used photographic equipment that we
    > > did purchased from customers.
    > >
    > > Sometimes, we need to know from who we did purchased this item, or when, for
    > > example a Tamron lens.
    > >
    > > My problem is : whenever a customer sells us stuff, he normally have many
    > > items : a body, one or two lenses, adapters, and so on. On each item, we
    > > list serial numbers, accessories, etc. So I've those are my fields :
    > >
    > > Department1 Brand1 Product1 Number1 Price1
    > > Department2 Brand2 Product2 Number2 Price2
    > > Department3 Brand3 Product3 Number3 Price3
    > >
    > > Etc. up to 10 fields...
    > >
    > > For example:
    > >
    > > Body Pentax ME Super 345678 150$
    > > Lens Pentax 50mm f/1.7 23345 50$
    > > Lens Tamron 70-210mm f/4,0-5,6 123423 80$
    > >
    > > And so on...
    > >
    > > So, whenever I'm performing a search (using serial number) over these
    > > fields, I've to put, for example, the serial number in a specific field
    > > (let's say field « Number1 ») and perform the find. No result. Okay.
    > > Let's try another field... « Number2 ». 2nd search, still no result.
    > > That's a pain in the butt...
    > >
    > > Any other way to perform a search over every field, using the serial number
    > > as the variable? =))
    Chris Brown Guest

  7. #7

    Default Re: Performing a FIND over multiple fields?

    Shadenfroh <shadenfroh> wrote:
    > Create a calc field for the column of fields. For example, "All_Products"
    > could be calculated to equal "Product1 & " " & product2 & " " & Product3..."
    But how do I make the calculation field reflect changes of information
    in the post? Can I force it to run from scratch everytime?

    When I used this with fm5 at least, data just added up and was never
    removed. It's possible the calculation was somewhat different though.
    I'll check when I get a chance.
    Michael Bystroem Guest

  8. #8

    Default Re: Performing a FIND over multiple fields?

    "Michael Bystroem" <michaellastnamemac. wrote
    > Shadenfroh wrote:
    >
    > > Create a calc field for the column of fields. For example, "All_Products"
    > > could be calculated to equal "Product1 & " " & product2 & " " & Product3..."
    >
    > But how do I make the calculation field reflect changes of information
    > in the post? Can I force it to run from scratch everytime?
    Perhaps it was looked up or stored or some such. Make sure that the option
    (under storage options) is selected to NOT store the results. It will be slower
    to search, but more compact and the calc field should always contain data that
    matches the current contents of the referenced fields. Any updates should
    immediately cascade through the calc.

    Kent


    Kent at home Guest

  9. #9

    Default Re: Performing a FIND over multiple fields?

    Kent at home <ca.invalid> wrote:
     
    It probaly was. I didn't make that field myself and didn't see the
    actual DB in Filemaker. I was looking at its values through Lasso. 
    Yes, but an unstored calculated field seem not suitable for Multiline
    key relation fields, as searches and relations then risk to crawl if
    unindexed. It would be better if FM could trigger the recalculation in
    the field. Now I try and do a recalculation whenever a new post is added
    or an existing one removed, but that caclulation have to be made inside
    the script and the result set to the field. Does feel unecessarily
    bersome granted that making a useful filemaker database does involve
    passing around keys and storing them in several places, one of them in a
    multiline key field for relations. Can't have an unstored result there.
    Michael Guest

  10. #10

    Default Re: Performing a FIND over multiple fields?

    Jean-François Landry <ca> wrote in
    news:BB6F193F.A679%ca:
     

    Maybe you can do a script, if nothing as been found in field #1, search
    in next one , and so on !
    Patrice Guest

  11. #11

    Default Re: Performing a FIND over multiple fields?

    Patrice wrote: 
    >
    > Maybe you can do a script, if nothing as been found in field #1, search
    > in next one , and so on ![/ref]

    A few thoughts:

    1. You can make a calculation field that concatenates the fields you
    want to search, then do a search on the calculation field. Just be sure
    that in the calculation formula you put a space (in quotes, thus " ")
    between each field name, something like this:

    Field 1 & " " & Field 2 & " " & Field 3

    You need the space between so that the Find will treat each field as a
    separate word.

    2. In FMP 6 (and probably 5), you can do a full text search of the
    database by using the menubar command Edit >> Find/Replace

    3. What I think is the best solution is to split the database into two
    related files, one for Customers and one for Items, related by a new
    field called, say, Customer ID. Customer ID in the Customer file should
    be a unique serial number automatically assigned when you make a new
    customer record. In the Item file, Customer ID should be assigned to
    each iitem from the Customer file. You can set it up with a portal in a
    layout of the customer file, so that all the items related to that
    customer appear in the portal. You can set it up to enable creation of
    related records from the Customer file, and do that in the portal.

    With this relational database setup, you type the customer info one
    time, and then fill in the items from that customer in the portal. Then
    you can do a normal Find on the Itme file to find the item you are
    looking for.

    And so on.

    See the on-screen help file for more info on setting up and uusing
    relational databases.

    Repeating fields are a hold-over from the early days of FileMaker,
    before it was really a relational database. They are still useful in
    limited cirstances, but in general you are better off using a
    relational database in situations where you want one-to-many or
    many-to-many correspondences.

    Bill
    B Guest

Similar Threads

  1. Replies: 2
    Last Post: February 17th, 01:17 AM
  2. Search multiple keywords across multiple fields
    By Darren Heinrich in forum ASP Database
    Replies: 3
    Last Post: February 6th, 05:29 AM
  3. Find mode - compare two fields
    By Scott in forum FileMaker
    Replies: 3
    Last Post: September 18th, 02:08 PM
  4. How do I find not-unique fields
    By Joseph Galron in forum FileMaker
    Replies: 5
    Last Post: August 6th, 02:19 PM
  5. Replies: 2
    Last Post: June 28th, 05:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139