Pulling names from main databse into another

[detailscreative]

I have my main customer database and database 2 that lists all the classes available to my clients each year (new database 2 each year). In my database 1 I have a defined relationship that goes to my database 2 and pulls the items back into database 1 as radio buttons (for data entry). I now need to see a list in database 2 showing all the names, emails of the people who signed up for each class listed in database 2. I can only get one person to show up under each class (regardless if there is 1 or 50 people signed up for that class). How do I get it to show me all names in alpha order?

[-Queue-]

What version of FM are you using?

[detailscreative]

oops sorry -- 6 for mac

[-Queue-]

Create a value list in db2 that uses 'only related values' from a full name field in db1, based on the class relationship. Then create an unstored text calculation of

ValueListItems( Status(CurrentFileName), "yourValueList" )

or

Substitute( ValueListItems( Status(CurrentFileName), "yourValueList" ), "

[T-Square]

Another way to accomplish a class roster would be to put a portal on a class layout.

However, based on your description, you might have a problem: How do you keep track of who is taking what? I suspect that you are storing the classes a person is taking in a text field for the student. However, the Student-To-Class example is the textbook example of a Many-To-Many relationship, which is best handled by use of a *join file*. The join file is a simple database file that at a minimum has a StudentID and a ClassID field. Each record in this file, then, represents an individual registration for a class (therefore, let's call it Regs). Create a relationship from Students::StudentID to Regs::StudentID that allows creation of Regs records and deletes Regs records if the Student record is deleted (cascading delete). Similarly, create a relationship from Class::ClassID to Regs:ClassID that deletes Regs records if the Class record is deleted.

With this more complicated structure, it becomes much easier to extract most of the data you'd need.

To create a class roster, you'd create a layout in the Class file with a portal to Regs. The StudentID in Regs allows you to draw in the complete student record if needed (usually a name or ID number suffice). Just put the fields from the Student file on the top portal row. They'll repeat in browse mode.

To create an easy class selection layout, create a layout in the student file with a portal to Regs. Define a value list in Class that uses the ClassID and ClassName, and assign this to a dropdown list control that is associated with the ClassID in Regs on the portal. Then, when you want to add a student to a class, you go to this layout, click the dropdown for class on a new row. Once you select a class from this list, you will have created a new Regs entry with the current StudentID and the selected ClassID. When you go the the Roster layout, the student will appear.

HTH.

David

[detailscreative]

I guess a portal is what I have used. This portal is in my class file and pulls that data from my main file. I can get it to pull one record for each class, but only one -- I cannot get it to list all the participants in each class

[detailscreative]

See th econference file -- attendance sheet layout -- what do I have wrong??

[Søren Dyhr]

Every time I hear a question regarding copying data from one base to another, or from one table to another ...I hear warning bells chime "Syncing issues and redundant storage" ...all issues a relational design is ment to cure or at least solve to a certain extend ...so I'm absolutely with T-Square's many-to-many approach, but where a table for the year are connected to the joinfile/table ...sorry JT but I think you're perscriping medicine, to keep the pharmacist in business and not overly eager to see the patients recovery???

--sd

[Magistrate2020]

I'm beginning to wonder if this thread might help my problem too, any thought?

http://fmforums.com/forum/showtopic.php?tid/167996/post/new/#NEW

[Søren Dyhr]

No in your case are we talking about historic documents that by nature contains some redundancy ...you can issue several documenst to the same client, with the same content of items even on the same day ...never the less should they exists with varying invoiceID's.

The catching issue you have is if you add a new customer on the layout belonging to the invoices, isn't it transfered to the clients for further use.

I'd better continue this on your thread....

--sd

[detailscreative]

Ok, so what is the answer here? All I want to do is generate reports (1 for each class) listing all the attendees. Is there a better (easier) way to do this?

[Søren Dyhr]

Yes have them in one file, make a found set and report!!!

--sd

[detailscreative]

That works, except I have over 75 classes that I need lists for each one. Doing it that way I would have to create a found set 75 times. Can't I use my database 2 to pull the names from my database 1, creating a report for each class all at one time - -then print them out 1 class per page?

[-Queue-]

Your fields are not completely contained within the portal row. Move your portal down to about 121 pixels from the top, and the related data will display correctly.

I do not believe there are any synching or redundant storage issues in the solution I proposed, because it is unstored (and used for a read-only purpose), which negates both issues. Also, because each year is in a different file (though I would not set it up this way), a join file may not be necessary and will probably be more confusing in this case. If you overhaul your setup and put all years into one file, then a join file will be much more appropriate and useful.

[Søren Dyhr]

Doing it that way I would have to create a found set 75 times.

I'm still wondering why it gives you more peace of mind, storing found sets is pretty easy, requirering a related table with 75 records, containing the ID's of each pupil the class consists of.

The way to sift the right class out from the heard is a GTRR script step from one of the 75 records. The storage of all ID's in the new table is a returndelimited textfield that act as relational Key ...thats it really. Instead of having 75 files floating around with the sole purpose to make a report.

--sd

[detailscreative]

I am not wanting 75 different files. I am wanting 2 files -- my main database file with all the names and my class file that lists all the classes. I have a portal on my main file that pulls all the classes into a radio buttoned data entry screen -- people can sign up for 1 or all 75 classes. I want to be able to get a list for each class listing everyone who signed up for it. Some people may be listed on 10 class lists. What I would like is a layout in file #2 call "Attendance Lists" where it would automatically go to file one, pull all the names for each class, then display them page by page in the Attendance List layout.

I must not be explaining this correctly or I'm not understanding what you are telling me to do -- but I am relatively new to FM5.

I don't understand how to get what I need from what you are telling me to do.

[Ender]

Hi details,

Looking at your sample files it's clear why you're having trouble getting your desired class lists. Because the classes are listed as radio button choices in one field, there's really not a good way to generate one report to show all the class lists. You could run a separate report for each class, finding for one class at a time, and listing the found students in a list view. But this is not a very good solution in the long run.

T-Square has outlined the structure for a typical Student-Enrollment system, where the Students are records in one file, the Classes are records in another, and Registrations (a Student-Class join) are records in a third file. This structure makes your class lists and other types of reports fairly easy, but also gives you more options for viewing the data and makes adding new modules easier.

I have redesigned your files a bit to work with this structure. It will likely need work to make it fully functional, but this should give you the idea.

You might also consider simplifying the member/non member items by having a member rate and a non-member rate for each item, then pulling in the correct rate based on whether the attendee for that class is a member or not.

On another note, I notice you have been adding fields to keep track of data for multiple years. I'd recommend that you instead include the Year as part of the class and enrollment records, and then filter your relationships to look only at a selected year. This is another thing that will be much easier to maintain in the long run.

Finally, you should probably not be posting files on the net that contain personal data about your clients. If needed, you can save clones of the files you wish to post to empty them out.

FMP_file_copy.zip

[T-Square]

I haven't looked at the various sample files sent out, but surmise from the discussion that my original description of your data was accurate. Ender's last points regarding the structure of your classes file should be underscored.

Instead of creating a new classes file every year, store semester/year information with each class--it's an attribute of the class, after all. Then, you can create a global field to store your active semester/year. Build your current class value list on a relationship built on a match of the global field to the class's semester/year. This will result in a dropdown list of the classes for the currently-selected global semester/year value. Another option would be to flag every class as active or not, then build your dropdown on only active classes, but this is a clunkier solution.

With regard to the tuition, you can store the member/non-member tuition rates with the class as well, and select the appropriate value depending on the student's member status. This will give you the best flexibility for handling course-by-course billing oddities. You will probably want to store the amount you charge in the Registration record, so that you have a stored record of what the person was charged.

One big benefit of adding this depth to your solution is that you then will be able to see a full history of each student's courses, regardless of the year they too the class. Another is that for classes that repeat from year to year, you can copy the old class record and change the specifics quickly.

HTH,

David