how to query db

[iprhummers]

I am new to this forum and need assistance with a query/search/find in a database – this is a bird banding project.

I have recently decided to switch from an old db program called Paradox to File Maker Pro. I was able to export the db from Paradox to an excel file and then import to File Maker Pro. FYI, I currently have over 6,500 records each with 45 fields. The problem I currently have is being able to search the File Maker Pro db as I had previously done with Paradox. The only way I know to pose the question on this forum is to note what info the needed query contained in the old Paradox db. So here is the example…

Band #....................Date

Join 1......................03/12/2008

Join 1......................>01/01/2004, <03/13/2008

I need to search records for birds processed on a particular date then find all records for those particular birds. In other words, if I have 50 birds recaptured on 3/12/2008, I need to find all the past records for those particular birds for the previous 4 years.

Any help would most appreciated.

Thank you.

[Ocean West]

in find mode type in the date range 1/*/2004...3/13/2008

you can use * wildcard. it will find all records in that range...

if you have a relationship setup between two table occurrences (different tables)

you could do a GoToRelatedRecord with the option to show all from the current found set.

This would show the parent records with this criteria assuming you did a find in the child table where the dates are stored.

Edited March 29, 2008 by Guest

[iprhummers]

I know how to find all records within given dates. What I don't know how to do is find all the records for the birds caught on a specific date as well as all the past records for each of those individual birds. In other words, if I recapture 50 birds on 3/12/2008, it will give me the band numbers for those birds. But I also need to see all those previous records for those 50 banded birds. Some birds have been caught over 25 times and I need to see all the past records for birds caught on a specific date. Yes, I could enter each band number separately - band number by band number and get the records for each bird. But that would take a very long time. With Paradox, I have been able to find all records for birds recaptured on a specific date as well as the past records for those individual birds. I have been able to do that with one search and not bird by bird - one search - one table.

There has to be a way to make two searches at once as I was able to do with Paradox.

Edited March 29, 2008 by Guest

[LaRetta]

As Stephen said, you want to use the Go To Related[] script-step. Since all the records are within the same table, go to your graph and create another table occurrence of the table (maybe name it ALL related bird dates). Join it on bird ID (or whatever you use to identify each unique bird; band?). Then, from your original layout based upon your original table occurrence, create a script similar to:

[color:blue]Go to Layout [ originalTableOccurrence::originalLayout ]

Enter Find Mode [ pause ] ... here you would type your date such as 3/12/2008 then hit enter

[color:blue]Set Error Capture [ on ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found" ]

Show All Records

Exit Script

End If

Go To Related Record [ ALL related bird dates ; existing layout ; match all records in found set ]

When you now fire this script (attach it to a button on your layout), it will pause to let you type the date of banding and, after you hit enter, it will find all birds on the date you want, then (by association, called Go To Related in FM), it will expand to include all records for any birds existing in that found set.

BTW, iprhummers, welcome to FM Forums!

LaRetta :wink2:

Edited March 30, 2008 by Guest
Added bold

[iprhummers]

Stephen and LaRetta…

Thanks for the help. I realize that it will take some time to figure it out. Not only am I new to the forum, but also I am new to FileMaker Pro and right now it seems all-Greek to me. It is so very different from my previous db, Paradox. I am still trying to figure out how to limit the number of fields in searches and do simple tasks like saving queries.

I will let you know how it goes, as I get further into the learning process.

Thanks again…

Barbara

[iprhummers]

Okay, I definitely need some step-by-step help.

On 3/22/2008 we captured 51 birds that had previously been banded at our site. I am trying to find out if those birds were first-time recaps for the year or had they previously been captured earlier in the year. I know from my query on Paradox that 42 of the 51 birds were first-time recaps for 2008. My query in my Paradox db tells me that there are 60 records for these birds captured on 3/22/2008 but only 42 are single records telling me that this was the first time they had been recaptured this year.

So I need to duplicate this search on FM. The records have been successfully imported into FM and the database is called HMN db. I have tried the script method to no avail.

Here are the steps and script I used…what am I doing wrong?

Find records for recaps on 3/22/2008 – 51 records found out of 6732 total records

Then Establish new script as follows:

Go to Layout [ originalLayout ]

Enter Find Mode [Restore; Pause] HMN db : Date: 3/22/2008

Set Error Capture [ on ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ “OK ; No records found" ]

Show All Records

Exit Script

End If

Go To Related Record [ Show only related records; Match found set; From table: “HMN db”; Using layout: ; New window]

I would appreciate any help.

Thanks…

Barbara

[Ocean West]

Barbara, are your records all in one table - a record is a band ID & a date?

[Ocean West]

Can you attach an export of your data (just the two fields is needed BandID & Date )

I can put something together for you if I had some sample data to work with.

[iprhummers]

Stephen...

Thanks so much for offering to help. Yes, all the data is in one table - all 6732 records - each having 46 fields.

I would like to export some data if I knew how to do that. I know how to do that with my old database but not with FM. I can figure something out, but it may take a while. Right now I do not know how to limit the number of fields with FM. It was easy with Paradox and I am sure it will be with FM once I figure it out. I know how to export in excel - will that work? I have only been working with FM for a few days and 4+ years with Paradox. FYI, the reason I would like to switch to FM is that the USGS Bird Banding Lab uses a FM based program called Bandit and I need to export to that program to submit data to the government. I am also working on my own studies so need to be able to search the database regularly.

Thanks again...

Barbara

[Ocean West]

you can export the data from your old file tab delimited is fine. Just need the two fields... i will import the data into fmp example.

When exporting in FM you need only choose the fields you wish to export they will appear on the right. "Export Field Order"

http://screencast.com/t/nAVVkPA9GCp

[iprhummers]

Stephen...

I have tried to send a txt file that is 212 KB, but it was taking a long time. How long should it take to upload and file and send to this site?

Thanks...

Barbara

[Ocean West]

go ahead and email it to me

[Ocean West]

Thanks Got the file...

followup questions from email (for benefit of forum)

when I do a find for 3/22/08 I find 77 records

44 of them have been captured more than once since 1/1/2007

51 of them have been captured more than once since 1/1/2004

is this the type of query you wish to result?

How would you like it displayed?

Do you have a table that a record is a single band ID?

i have created a loop that will generate the table. from the banding data.

I based on this I will post the file back.

[iprhummers]

Stephen...

I have emailed you the results of the 2 searches via Paradox. I need to duplicate these searches in FM Pro.

Hope this helps.

Thanks...

Barbara

[Ocean West]

Barbara I hope this works for you... you have a fascinating process

I am sure there are other possible methods to employ to derive the same data. Perhaps others can provide an example or too.

http://screencast.com/t/1eft2HXzLV

birds.fp7.zip

Edited April 1, 2008 by Guest

[Ocean West]

here is yet another option using relationships...

http://screencast.com/t/Rs4vM9XnJGt

birds.fp7.zip

Edited April 1, 2008 by Guest
added link to video

[iprhummers]

Stephen...

WOW! I'm impressed! It will take a while to absorb what you have done and figure out how to use it. But I can't thank you enough. I assume that I can add a few more fields - like age and sex - correct? Can I do that by using the "Define mode" and then importing the db once again?

I am certain that I will have a few more questions. But thanks a million.

Barbara

....................................................

Barbara Robinson

Master Bander

Hummingbird Monitoring Network

Indian Peak Ranch

Edited April 2, 2008 by Guest

[Ocean West]

Yes you can add the fields to the "data" table, you can delete all records from that table then reimport the data from your originals including all the fields you wish to see.

then on the layouts and in the portals add these new fields.

glad I could help.