I can't seem to figure out how to gather a found set of the records I need displayed...???

Posted on 2007-08-06
Last Modified: 2013-11-05
I have a table called Auctions that has both an ItemNumber and a ScanID in it.  ScanID's are unique to ItemNumber's, so if I have 10 auction records with the ScanID 12345 they should all have the same ItemNumber.

Somehow I've gotten lots of auction records that are getting incorrect ScanID's.  I need to figure out how, but right now I simply need a way to find all of the problem records.  

I can put a ! in the ScanID in Auctions to get a found set of records with duplicate ScanID's, however, I don't know what to do from there in order to filter so that I only see records with duplicate ScanID's but different ItemNumber's.  

Any information would be greatly appreciated.  Thanks!
Question by:Andrew Angell
    LVL 28

    Accepted Solution

    maybe you could create an extra temp field showing if scanID=Itemnumber. this way you would see the guilty ones once you've got the right found set, and restrict the found set to these.
    LVL 11

    Author Comment

    by:Andrew Angell
    I wound up creating a calc. field in Auctions that allows me to more easily find the problems.  The calc. I created is:

    If (
    ScanID ` "" ;
    If ( Item_Number = AuctionsToScansByScanID::ItemNumber ; 1 ; 0 ) ;

    I just store it as text.  Not all items have ScanID's because they didn't get introduced until just a few months ago.

    So then I search on this to show me only items with invalid scan id's.  That helps a lot.  The problem now is yet another stupid thing with our system.  This ScanID field, before we started using it for our own ScanID, was being used to store an item ID from another company we were working with.  Many auction records with ScanID's are actually this other company's ID, not ours.  They're easy to spot because theirs have a few more digits than ours.

    Is there a way I can do a find displays only records where this field is less than 8 characters, for instance?
    LVL 11

    Author Comment

    by:Andrew Angell
    Also, on this same situation, how can I add validation to the ScanID field?  Now that I have the calc. that shows whether it's valid or not it seems like it should be easy to add validation to popup a message alerting with the ScanID is not valid and won't accept it.  I'm confused over the validation calc. dialog for some reason.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now