The trick is to make your report based on your "rooms table".
You then do a find for the student Id you want to display.
On the header (or the title header) you can put fields related to the "Student table"
So the steps in a script would be: (While you are on a layout based on the Students table)
Set Variable - the StudentId ($StudentId)
Go to Layout - The print layout layout
Enter Find Mode - Clear the pause box
Set Field - Set the field "StudentIdFk" in the Rooms table to the value of $StudentId
Set Error Capture On
And then, depending on whet you want to to, either Save PDF of Print and
Go to Layout Original
This sounds like a standard invoice. Search this forum for posts on Invoices or similar and look for a link provided by PhilModJunk to a sample file for creating an invoice by Comment. The students will be the contacts, the invoice will be the main data entry layout, products will be a list of all the charges and the line items will be where everything comes together. Comment also included a layout for printing and script make it all happen. You will need to flesh out the data fields for contacts based on your needs, but this will give you the bones on which to hang the gooey parts.
So far I can't find the link you suggested by PhilModJunk.
Can you give me a little help on this?
Never mind, I think I found it.
Although I would love to hear from PhilModJunk on my issue.
Ok I just looked at an invoices demo by Comment and the Print Invoice isn't helpful at all based on my situation.
How about this as a little example:
I don't know if I have your structure all down or not, but you might be able to get the idea.
I'm checking it out right now.
As to my structure, its all set and the entire program is working.
Just need to produce the report.
I get the example, but my structure is a little more complicated.
First there are multiple connected tables to the student. Not just Rooms, but Kitchen, Bathroom, LivingRoom. Each student has multiple room tables attached to them via studendId. In each room table, there are anywhere from 10 to 20 fields. Each field describes a specific issue with the room. Like the Kitchen table has CleanOven, ReplaceMicrowave, GeneralCleaning with associated cost.
So my report my get the student, go table by table (room by room), then in a room check for any fields that are not zero(that's a charge) and then that becomes a line item for the invoice.
I am uploading some images so you can see what it looks like.
Wow, looking at that relationship graph I nearly fell of my chair :p
I can't see the other screenshots very well, they are kind of small. Could you give some larger shots of your layouts?
I think your structure needs a little re-thinking.
This doesn't really look like good database design. And it doesn't look very flexible at all.
I'm not surprised you are running into trouble making a report. And I thinnk you'll be running into some more trouble as you advance with this database.
I tell you what:
Try and get me a few better screenshots, I'll have a look and a brainstorm, and we'll see if we can't come up with a better, cleaner, more intuitive solution.
One that's more flexible, and gets you more stuff done in an easier way.
Because I honestly don't know how to start making a report in this structure.
You might be able to solve this in a simpler way. However I'm a little bit afraid it will only work once. In other words, you fill out all your rooms tables and then you make an invoice, once.
What you could to is go to every room table, search for the studentId.
And then if it's found, import those found records to a line items table.
You do this for all the tables and by the end, all your items and amounts are in one lineitems table, linked to one student.
So you create a new table (If you don't already have it) And you call it: "LineItems"
You create a script that:
- Set's the student Id as a variable. (so this script is best to be started fom a layout based on the student table)
- Then it goes to the first room table, (Go to Layout)
- Set field (set the studentId variable to the StudentIdFk field of that table
- Perform find
- If Get (LastError) = 0
SetField - Set the StudentId in a global number field so you can import it in the invoice table (Or use an InvoiceId if you have an Invoice table)
Import - Import the found set into the lineitems table. Make sure you import the records you need. And that you put the student ID global field in
the correct field in the line items.
And this goes on and on for every table.
It's not such a stylish solution; But with your structure I think it's not going to be easy.
Here's a fuller view.
I think the reason we initially made the inspection screen this way was the people doing the inspections were using the ipad and the layout made it work visually for them. The second thing was the sheer number of fields in these tables was way too large for one table.
Is there anyway I can send you my database with most of the students removed? If it would help, I'd don't mind.
I'll send you my email in a private message
This solution still doesn't quite capture whats going on.
There is only 1 student main record and each student has one record per room, with between 10 and 25 fields in that room.
You mention import those record(s) into a line item table. But again its just one record with many fields (see the inspection layout)