Pulling data from linked table
Thanks to you all, I've learned quite a bit. I have a table called Players and another called Roster. I have linked Roster > Players (name). When I choose the appropriate Player, I want the Roster table to also display the 2 fields; Salary and Points from the Players table. I began using formula fields but not sure how to make that work. What is the best solution?
12 replies
-
@Doug,
Not following the statement "I want the Roster table to also display the 2 fields; Salary and Points from the Players table.". The Roster table has a zero to many relationship with Player table.
What you can do.. is look at it from the Players table.. and add the Roster to a view of the Players.
-
Thank you Mconneen, I didn't explain well enough. In your example, Your Roster table has the information I need, where I call it the Players table. What I want to do is build a roster from that table. Where I would choose "Player 1", and when I do it would also display his salary and pts... in what I call the Roster table. (This db is for a fantasy football project where I would build different rosters weekly.) That said, I want to build the Roster table from the Players table. If I'm still vague I'll try and come up with a way to better explain. (It's not you, it's me.) :)
-
How's this? (Is what I should have done in the first place.) I want to build this:
From this:
-
Do you want something like this, or a different related table for each position, or something else?
-
Yes sir and then I'll keep a running total of the players I select. I appreciate your help Dean.
-
@Doug.. and it sounded like you wanted to change / create a new roster per week..
-
Yes sir. That is correct.
-
In addition if this helps, and I wish I would have thought of this sooner, it works perfect with VLOOKUP in Excel.
-
Oh, you want to do a lookup that pulls data from the Players table to unrelated fields in the Roster table? Similar to selecting from a Choice field, except the choices are records from the Players table? I just posted four screenshots from a barebones solution that does that, at:
-
With this approach, the solution could look like the screenshot below, with the Players reference field on the far right acting simlar to a Choice field, with its choices coming from the Players table. The unlink icon could be clicked and then another Players record could be selected from the list of Players. This would activate the "Trigger after update" property of the Players reference field:
Position := Players.Position;Player := Players.Player;Salary := Players.Salary;Extrapoints := Players.ExtraPointsI am not saying that this is the best solution. It would just be similar to the lookup action you mentioned.
-
Thank you so much. I had talked myself out of using a choice field and then wasn't sure what to do. What a great help you've been.
-
The below arrangement with three tables seems to work. The "Players" and "Lookups" tables have the same columns.
Content aside
-
1
Likes
- 4 yrs agoLast active
- 12Replies
- 1400Views