Lookup or if help to populate list

[CarlosFitz]

Hi there, I appreciate any help, I have two tables and I need one of them filled with the info from the other one

 

ID Name Subject 1 Subject 2 Subject 3

23 John      

34 Tom      

Id Name Subject Order Subject

23 John    3                 Math

23 John    1                 Bio

23 John                       Geo

23 John    2                 Reading

34 Tom     2                 Math

34 Tom     1                 Geo

And I want to populate the first table as this:

 

ID Name Subject 1 Subject 2 Subject 3  

23 John    Bio         Reading    Math

34 Tom     Geo        Math  

Is it possible with any kind of lookup or if function?
Thanks

Edited July 29, 2014 by Lee Smith
Removed the large amount of white space

[Lee Smith]

Why don’t you attach a copy of your file or a mockup.

 

To attach a file, just follow these simple steps you will find here

[comment]

Is there a reason why a portal would not achieve what you want? I don't quite see why you want to exclude the 4th and subsequent subjects from being displayed, by you could do this by filtering the portal to display only records where the Order field is not empty.

[CarlosFitz]

 

The problem with the portal is that it shows in vertical list and I need to have the subjects ordered in horizontal line, one line per Name with all their subjects. (I don´t know if the portal can be shown in horizontal line)

 

I excluded some subjects because there is a large list of subjects and the people only choose three of them and they rate it (SubjectOrder)

 

I uploaded a mockup

lookup.fmp12.zip

Edited July 29, 2014 by Lee Smith
Removed extra returns.

[comment]

You can use three one-row portals, placed side by side. Set the second portal's initial row to 2, and the third portal's to 3.

[Lee Smith]

Hi Carlos, and welcome to the FMForums.

 

Please do not use multiple returns. Using double returns are standard, anything more is distracting.

 

Thanks

 

Lee

[CarlosFitz]

 

I already tried with the one-row portal it´s a neat idea, the issue I found is that I can't export via excel the populated list, in excel the one-row port shows as "0"

 

Edited July 30, 2014 by Lee Smith
What part of not using multiple returns didn’t you understand

[comment]

You didn't say anything about export until now. This complicates things considerably, because - unlike filtered portals, that work on layout level - exporting is done from the data level. To arrange your data for such export, you will need to add a dedicated relationship that includes only ranked records, as well as three calculation fields using the GetNthRecord() function in order to fetch the top three related records from this relationship.

 

Personally, I prefer leaving the solution free of any artifacts whose sole purpose is to enable exports, and handle this by exporting as XML and letting a custom XSLT stylesheet re-arrange and format the exported data to its intended form. However, this requires some XSLT skills.

[CarlosFitz]

Thanks, I´m trying the XML option I´ll let you know

Lee: this post is with firefox

[Lee Smith]

This sure sounds like Safari has a problem on your machine. FYI, Safari has been the root of problems in the past. Did you try flushing the cache in Safari Or Reseting Safari?

 

Did you Repair Disk Permissions?

 

Lee