This is most definately NOT a basic DB question.
Your screen shot of your Relationships gave me the clue I needed.
I believe you need an additional relationship.
You have two Join Tables: BookLabsLine and LabMaterialLine
With the relationships graph open, drag from BookLabsLine::Lab_ID_fk to LabMaterialLine::Lab_ID_fk. You'll get a "Table Instance" dialog popping up. This is not a new table, but it will look like one on your diagram. Give this new instance a name Like BooksMaterialsLine.
Use the same method to link an Instance of Materials to BooksMaterialsLine.
Now refer to your fields using these two new Table Instances in your layout and I think you'll get what you need.
I'm going to sleep on this one and check back in the am. My first post was to get you started with relationships that can make this happen if you set up your layout the right way. I'm going to double check my advice to make sure that I gave you good advice and then take a crack at the next step.
The main issue here is that you have multiple many to many relationships--this is the design you need--but the devil is in the details.
Here's a quick check for understanding. This isn't a solution, but a check to confirm my initial analysis is correct:
You need the following report:
Book 123 Principles of Chemistry 3rd Edition
Lab 145 Basic Observation
Material 234 Candle
Lab 236 Electrolysis
Material 101 Battery
Material 202 Wires w/alligator clips.
and this is the report you can't get.
Table relationship summary:
For any given book record you have multiple Lab Records.
For any given lab record, you may have multiple book records.
BookLabsLine functions as a "Join table" linking many book records to many Lab records.
For any given Lab you may have many materials.
For any given material, it may be used in many different labs.
LabmaterialLine functions as the Join table linking many labs to many materials.
The challenge lies in linking the two sets of many to many relationships.
That's what I've been mulling over for you off and on since my initial post. The light bulb hasn't come on yet, but I'll keep thinking.
In the meantime, maybe the above post will prompt another Forum participant to post a solution suggestion.
That is the correct report, except I don't really need the Lab names in it.
All I need is:
Book 123 Principles of Chemistry 3rd Edition
Material 234 Candle (Put 10 into a classroom kit) (put 1 into a workshop participant's kit) (it is reusable)
Material 345 Test tube (Put 10 into a classroom kit) (put 1 into a workshop participant's kit) (it is not reusable)
Material 456 matches (put 1 into a workshop participant's kit) (it is reusable)
Material 135 Lead Nitrate (Box of 12 1 oz. Botles into a classroom Kit) (Not reusable)
Quantities and whether the thing is reusable or not is taken care of. I just need to get all of the materials separated by category as above onto a one piece of preintable paper so the warehouse can fullfill the order of a, say, classroom kit.
As far as your relationship summary, that is exactly how it works. I can feel that it really isn't too complicated, I just don't have enough experience with databases and even less with Filemaker to make it work.
Is there a way of creating a custom query in Filemaker? I am used to programming MySQL and if I can make a "SELECT `Mat_ID_fk` FROM `LabMaterialLine` WHERE `Lab_ID_fk` = "##" OR `Lab_ID_fk` = "##" OR `Lab_ID_fk` = "##" etc..."
My issue would probably be solved.
Actually, leaving out the Lab names eliminates referencing an entire table, so that simplifies things.
Which table and which field or fields holds the Science Items, Household Items, Liquid Chemicals information?
Is there a way of creating a custom query in Filemaker?
We call those find requests.
I am used to programming MySQL and if I can make a "SELECT `Mat_ID_fk` FROM `LabMaterialLine` WHERE `Lab_ID_fk` = "##" OR `Lab_ID_fk` = "##" OR `Lab_ID_fk` = "##" etc..."
Whether you are using a database tool that uses SQL (I've worked with MS Access SQL a lot) or FMP, it still comes down to the relationships. In FMP you are using the Relationships Graph to connect records and tables. In SQL your expressions will use "Left join, Inner Join" type statements to link tables. The inherent logic is the same.
material_category_fk in Materials table. At first, I was going to use 0's and 1's in the Materials table and separate them that way, but then I ended up making a separate table Materials_category
THis is just to let you know that I haven't forgotten you. I've been playing with a model of your DB tables and think I have a method worked out. It uses a text field that functions as a kind of "Intermediate Query" (to lapse into SQL speak) to get us what you want.
Unfortunately, it's quitting time here and I don't have the time to finish testing and then post the rather detailed answer you'll need to make this work. I'll try posting the solution to you tomorrow.
OK, thanks to the wonders of a good night's sleep and some free associating, I've got the answer and it's actually pretty simple. I had another approach last night but it was a clumsy "Kludge" while this morning's idea is simple and elegant.
Return your relationship graph to it's original state. No need for new tables or additional relationships.
Open up Manage | Database | Fields and add a global text field gTitle to your Book table. We'll use this field in the report.
Dismiss Manage | Database and enter layout mode.
Create a new layout, called "Report" that specifies LabMaterialLine as its table.
Make it a List/report type layout and give it the following details.
Place gTitle in your header
Create a SubSummary part when sorted by Material_category_fk that "prints above" the body.
Place material_category_fk in the Sub-Summary part. From your earlier post, I am assuming that this is a text field that contains "Household", "Science"...
Place your detail fields in the body. If you need fields from Labs, Materials, or Materials_category, you should be able to add them here and get righ data to appear.
Save and return to browse.
Create the following script:
Set Field [gTitle, Book Name]
If [Count(BooksLabsLine::Book_ID_fk) = 0]
Show custom dialog ["no BookLabsLine records exist"]
Go To Related Record ["Show Only Related Records", From Table: "BookLabsLine", Using Layout: "BookLabsLine" (BookLabsLine)]
Go To Related Record ["Show Only Related Records", match found set, From Table: "Labs", Using Layout: "Labs" (Labs)]
Go To Related Record ["Show Only Related Records", match found set, From Table: "LabMaterialLine", Using Layout: "Report" (LabMaterialLine)]
Sort Records [Restore, no dialog] --- In this sort specify that records be sorted at least by material_category_fk so the sub summary part will be visible
I've referred to the default layout names that are automatically created when you first create a table in filemaker. In each Go To Related Record step, save the last, you can refer to any layout that refers to the same table shown in parenthesis. If you specify a layout in a Go To Related record step, script takes you to that layout and creates a found set of records satisfying the relationship criteria you specify. Unfortunately, GTRR will not produce the expected results if there are no matching records. Thus, the Count() function confirms that there are matching BookLabsLine records before permitting the script to continue. You would be advised to design in additional safe guards to insure that at least one matching Labs and labmaterialline record has been created.
Now to use the script.
Go to a layout that lists your Book records.
Select a record. You can perform a find. If it's a form view layout, you can click through the records using the book control in the upper left corner of the task bar. If it's a list or table view layout, you can scroll to the desired record and click on it to make it the current record.
Now run the script and you should see the desired report.
Thanks Phil, i feel I am closer to it than ever before, however, here is my script. I wasn't able to get some things into it as per your post.
For example, I couldn't enter: Set Field [gTitle, Book Name] and Sort Records [Restore, no dialog]
Here is a picture of what I was able to get and it doesn't go to the report layout after I run the script.
Let's fix the minor details first. Both of these shouldn't affect the main problem, but first things first.
- Open up your script in the script editor and click the Set Field step.
- Click the upper Specify button.
- In the pop up, select the Book table from the drop down menu and click gTitle to highlight it.
- Click the lower specify button to bring up the Specify Calculation dialog.
- Select the Book Table from the drop down menu at the top and then double click gTitle to select Book::gTitle as your expression. (You did define this as a global field in Book right?)
- Click OK to return to the script editor
- You should now see Set Field [Book::gTitle, Book::Book Name] in the script editor.
Now click on the Sort script step.
In the bottom left corner of the script editor a "perform without dialog" check box appears, click it.
Your script should now have all the correct steps.
Now for the main problem:
Go To related record will not switch you to a specified layout if there are no matching records for the specified record/found set and relationship. If you are using FMP advanced, use the script debugger to step through the script one step at a time and see what breaks.
If you don't have that tool try this:
What layout do you end up on after you run the script?
If you see BookLabsLine, then there were no matching records linking any of the records in the found set in BookLabsLine to Labs.
If you see Labs, then you there were no matching records in LabMaterialLine.
If all else fails, insert pause/resume after each GTRR step and run the script.
You can then check the results of each and press Enter to make the script continue to the next pause.
Well, I fixed the script and thatnk you very much for helping me with that.
However, I do end up in BookLabsLine table view. Oddly,I obviously have Lab_ID-fk in the labs field in that table because it shows me the names of the labs associated with the name of the book.
Here is a screen shot:
Also here is a screen shot of the Layout view:
and this is what I see after I go to the Book Table and run the script:
Ok, here is one more interesting thing.
You see how when in the BookLabsLine when I have Lab_ID_fk field showing in the layout it shows Lab Name? When I try to tell it to show the actual Lab ID number by saying show me "Labs::lab_id" it just shows me an empty box.
Maybe that will help.
Both of your previous posts suggest the same thing:
That you have something wrong with the relationship linking BookLabLine to Labs. Since GTRR can't find records in Labs that match the Lab_ID_fk values in BokLabsLine, it leaves you at the previous layout, BookLabsLine. The empty field you see tells me the same thing.
Try using find to search the Labs table for Baking Soda Cannons or any of the other Lab_ID_fk values you show me. Verify that they are EXACTLY, letter for letter the same.
If so, check the field definition for Labs id and Lab_ID_fk. Both fields should be indexed text fields.