Multiple Subset Relationships

[sburech]

I have a table of 50 records. Sets of five from the 50 records are to be related to single individual records. In other words, participants will select a set of 5 from the 50 records. One or more records in the table of 50 records can therefore be selected by one or more participants. The data in the 50 records will change weekly but the five records from the fifty selected by each participants will not change. I am baffled as to how to set this up. Thanks for your attention.

[Vaughan]

Generic descriptions of a problem usually don;t supply us with enough information to help.

Describe the *problem* you wish to solve. Avoid implementation detail.

Example: "I want students to be able to select courses based on the faculty the student is enrolled in."

Not: "I want a script to update fields ..." Scripts and fields are implementation detail, and they might be implementation methods are that are not optimal.

[comment]

As Vaughan says, it would be easier if we knew what is behind the solution - at least we would know what to call things. It seems you have a many-to-many relationship between participants and : (the table with 50 records), so you should probably have a third table for individual selections. I am saying probably, because it really depends on what you hope to achieve with these selections once they are made.

[sburech]

Thank you for your prompt responses. Specifically, we are studying the stock market. I have prepared a table of 50 stocks, from which students in several classes are to pick five and invest a mythical $10,000 which will be divided equally among the five stocks. I also have a table on each student consisting of the grade, class, birthdate, test scores etc. I would like to relate the student table with the stock table. My goal is to be able to tell each student how he or she is performing each week in each of the 5 picks and in the overall portfolio of the five stocks in general, after the students, of course, do the calculations themselves. I also would like to show on a printed chart the ranking of each student's portfolio via vis the others; how many students selected each particular stock; how each stock is progressing or depressing; how well the individual student portfolios of 5 stocks are progressing or depressing and how the market of the total 50 stocks as a whole is progressing or depressing.

[comment]

I believe you should go with the above suggestion:

Students -< Picks >- Stocks

In the Picks table, you'll need (at least) fields for:

• StudentID (link to the Students table)

• StockID (link to the Stocks table)

• Quantity

• PurchasePrice

[sburech]

The join table you suggested called “Picks” relating the five stocks to the students who selected them seems to be working well. I thank you for the suggestion. Now I am having difficulty in determining how I show in a report or reports the absolute appreciation (or depreciation) and as a percentage of the five stocks that each student picked for his or her portfolio; each student’s portfolio ranking via viz the other students; the frequency of each stock selection by the students; and how the entire selection of all stocks fared as a percentage (to demonstrate indexing) compared with each students picks. Thank you for your kind help.

[comment]

The gain/loss of each pick is easily calculated by comparing the PurchasePrice to the current price in the related stock record, e.g.

Stocks::Price / PurchasePrice - 1

returns the gain/loss as percentage. Use summary fields to calculate the totals per portfolio and in total. You should also learn how to use the GetSummary() function in calculations involving summary field results for a group of records.

You'll probably want to to produce separate reports for portfolios (i.e. Picks grouped by StudentID) and for stocks (Picks grouped by StockID).

[sburech]

Your were correct that gain/loss is easily calculated by stock::price / purchaseprice – 1.

However I am at a loss on how to group and total the gains on the 5 stock picks of each student and how to implement your suggestion that I produce a separate report for portfolios (picks grouped by Student ID) and for stocks (Picks by StockID).

[comment]

Well, you have quite a few requirements and it would take me many pages to cover all of them - so let me take one as an example:

Let's say we have these fields in Picks:

• StudentID

• StockID

• Quantity

• PurchasePrice

• cCost = Quantity * PurchasePrice

• cValue = Quantity * Stock::Price

• cProfit = cValue - cCost

• sTotalCost (Summary, Total of cCost)

• sTotalValue (Summary, Total of cValue)

• sTotalProfit (Summary, Total of cProfit)

Now create a new layout based on the Picks table with only these parts:

Header

Sub-summary (when sorted by StudentID)

Footer

Place the students name field from Students and the three summary fields in the sub-summary part. When you sort the records by StudentID and enter Preview mode (or print), you report should look like this:

Adam

• sTotalCost: 10,000

• sTotalValue: 8,400

• sTotalProfit: -1,600

Betty

• sTotalCost: 10,000

• sTotalValue: 12,534

• sTotalProfit: 2,534

...

[sburech]

I see that I am getting over my head, because I have allowed students multiple tickets entitling them to sets of 5 picks, depending on how well they did in the course. This means the student table is now two tables away from the picks table because there is a ticket table in between. When I print the report, I cannot bring up student names, only ticketID's to do the rankings you suggest. Is there a tunneling procedure for getting data from non-adjacent tables? Perhaps I should have picked an easier data base to learn FMP7. Again thanks for you patience.

[comment]

Don't see that it makes much difference - assuming you still want to summarize by tickets. Just make the sub-summary by TicketID, instead of StudentID (and adjust the sort order to match). You can place the student name from Students in the sub-summary part, same as before - except now it will show the same student several times.

[sburech]

I have the report listing ticket numbers and the stocks selected and their gains or losses under each but when I drag the student name into the subsummary field, it just shows up as a blank field.

[comment]

It should work, provided your relationships are set up correctly. Tickets should be related to Students, matching on StudentID:

Students -< Tickets -< Picks >- Stocks