how to make a summary field

[barbaras]

Hi everybody,

I'm not sure to be in the right place, but anyway I'll try to develop my problem.

I've a "classic" DB with record for biological products. Each product, is stored in a box (field "box") and in a specific spot (field "spot") in this box (there is 81 spots in each box). What I would like to do is to be able to check the number of empty spot available for each box. Of course, I can do a layout with a list but I would like something where I can see for box "toto" there is 12 spots available, etc.. . I got my nose on it for days and I can't handle it !

So, help

Thanx a lot

[ESpringer]

You can set up a self-join relation called SameBox (say), where each record is related to others in the same box:

Products:Box::Products:Box

Then, supposing you have some unique identifier ID in each record you can define a calculation field:

81-Count (SameBox::ID)

This should return the available slots.

You might eventually want to have a related DB for the boxes, where each box has a record with fields for name and capacity, and a relation from it to the main DB which taps all records mentioning that box. This would be good if you ever had boxes with different capacities. Then you could have that DB do a calc to show the capacity minus the number of items in it.

You don't ever have to *look* at this database once you set it up. Create a relation in the other direction, Products:BoxName::Boxes:BoxName and slap a field onto your layout that shows the calculated field for available slots.

Let us know whether this is clear enough to help.

-ESpringer

[barbaras]

Thank you very much ESpringer,

It works perfectly. The first suggestion, because the second I'm not sure I understood everything so I probably did something wrong. Anyway, It works and I'm perfectly happy.

So, to go further, I would like now, to be able to visualize each box with their content (the name of the product would be enough) and to have the possibility by clicking on the product name to go to the corresponding record (like a web hyperlink). I can do that with a "list view" layout, select the product name and then switch to the "form list" layout, but it's something a little bit painfull.

Thank you for your help,

[ESpringer]

If I understand you correctly, what you really should do now is make a file specifically for the boxes, with one record for each box. Then you can get that file to access info on all the items in it by creating a relationship to the Products file. From there it will be very easy to visualize any given box's contents, find the box with the most available slots, etc.

You claimed that you did something wrong in trying to set up the second thing.... what did you try, and why do you think it went wrong?

Here's another try at explaining what you'd need to do:

Create a "New Database" called "Boxes.fp5" (or whatever) and define a text field for "BoxName".

Then create a relation called "ItemsInBox" (or whatever) that works like this:

Left-hand key: Boxes[database]:BoxName[field]

Right-hand key: Products[database]:Box[field]

Probably, you'll want to sort the relation by PositionInBox (or whatever you call it).

Now the boxes database has a connection to all the Product records that refer to it.

To work with these connections, make a Portal in your main Boxes.fp5 layout.

(You'll probably want to give it a vertical scroll, since you have up to 81 items.)

Then, in each portal, drag a few Fields onto it, specifying that the fields come from the Products database. Probably you'll include the item name and location in box, or something like that.

To make the "hyperlink" work from the portal, select the field with the product name and format it as a button. Make it Go To Related Record, according to the relationship you've just made (ItemsInBox).

Now you should be able to get not just a number showing available slots (which you already have now from the Products database), but an overview of the contents of each box, with quick access.

By the way, you can also now set up a similar hyperlink in the other direction. Define a Relationship between Products and Boxes (just like the other relationship, but working from the other side). Now find the Box field in your main layout for the Products database, and define a button so that clicking on this field takes you to the related record in the Box file. (You'll still be able to tab into it to edit the info.)

-ESpringer

[barbaras]

Thank's a lot,

I did the first part of your new post but I simply messed up the empty slots calculation. I feel so stupid !

So, now everything works fine. The "hyperlink" stuff looks so simple after your explanations !!

For me, I thing the way is long long to be on top of Filemaker.

but anyway, once again, thank you for your help

[ESpringer]

Best of luck. In my experience, the best route to becoming fluent with FileMaker is to enjoy experimenting -- making sure that you're working on COPIES when it's something risky.