Searching portal records

[mjgwagner]

I've got a library database and I have a sub-table where I store checkout data. I need to be able to search the sub-table for those items that are checked out. I'm having trouble moving through the sub-table records. I can find the first occurrence of the related record but not any of the subsequent records. Any suggestions would be greatly appreciated.

On another note, what was the first version of FileMaker Pro that was native OS X (Power PC not Intel)?

Thanks,
Mike

 

[gmannatl]

Can you give a little further info regarding the relationships between the parent and child tables. Also, how are you determining whether or not it is checked out? For example is the parent table the customer info and the child is a link table for a many to many relationship with each entry in the portal being check in/out activity?

Also concerning the OSX question I can not help as I am a Windows user.

 

[mjgwagner]

The parent file contains all the specifics of the book or video (name, author, copy right, etc.) One of the items it has is what's called a call number made of of a topic category, sub topic category and a sequence number. The child also contains this call number as well as the fields for the person who checked it out, the check out date, due back date and returned date. To determine if it's checked out one checks that the checked out date for an item contains a date while the returned field is empty.

I can run the script and it will go to the first record of the parent table and then pull up the first record of the child table (assuming that the item has a related record). After that is continues looping in the portal and never shows any other related records in the child table regardless if it has any and never exits that loop.

I'm using the go to record/request. page [first] record script step for the parent table and go to portal row [first] for the child table. I'm using the go to record/request/page [Exit after last, next] to increment through the parent table and go to portal row [Exit after last, next] to increment through what I hope is the child records with the matching call number.

I'm also running FIlemaker Pro 5.

Thanks!

 

[gmannatl]

OK, here is the best way to do this. You are trying to run the script in the parent table which adds much more complexity because you are having to navigate each portal record within each parent record and then move to the next portal. The very simple way to do this search is from withing the child table. Since you are using FM5 it is a separate file. If you go to this file and simply do a find where the checkout date is populated and the returned date is empty it will give you all the results with NO SCRIPTING needed. You can then display related fields from the Parent table and quickly build a report of rented movies etc.

 

[mjgwagner]

I had experimented with that approach initially and I'll look into it again but if my memory serves me correctly the challenge I ran into was that the call number in the child table is a stored string but in the parent table it's a calculation so that was giving me trouble extracting the book title for the report. the three components that make up the call number are stored separately in the parent table but I haven't been able to get Filemaker 5 to let me set up three different relationships for the comparison (I also store the three separate items in the child table).

M

 

[gmannatl]

Are the "call numbers" the same in both tables. If so, then why aren't you just performing a lookup of the values based on the relationship if the values change in the parent, or even just showing the related value directly if they do not. I am not sure I understand the need to have this data duplicated in both tables. The relationship should be based on a "key" field and not this "call number" for example

Movie Table/File

MovieID
Title
Director
ETC.
Cat1
Cat2
"Call #"(calc of Cat1 etc.)

Rental Table

FKMovieID - Related field from Movie Table
"Call #" Auto-Enter Lookup(Movie Table::Call #")
RentedTo (Value List from Customer Table)
CheckedOut
Returned

 

[mjgwagner]

The call number is the key field although it's a pain being a calculation on one side. The Topic could be Santa Fe Railroad, the sub topic could be container trains and the sequence number is simply the sequence of when the book or video was obtained (004 is the forth item procured in the topic/sub topic combo). All three are needed to uniquely ID an item in the library. What the help function stated was that the field referred to in the "other" table, if you will, couldn't be a calculation but an actual stored item. I'm going to experiment a bit with the report and see what I can get it to do.

M

 

[mjgwagner]

I've got the report working somewhat. I set the calculated field in the parent file to be stored. The challenge at the moment is to determine why some of the records aren't getting pulled.