Aug 25, 2022 09:59 AM
I’ll do my best to describe this.
I have a base with two tables. Table 1 is an import of course records in a csv file. Table two is linked to a sanitized view of this data. Each course has a unique identifying number. Some of these courses are what we call “cross listed” which means that several different majors are in the same class. Example: The same class might be BIO100 and CHEM100. When this happens, the registrar puts a code to connect all the classes that are cross listed.
What am I trying to do?
I only need to see one of these classes for what we are trying to do. Is there a way to return or filter a “sanitized” list that could do that? I can try to provide screenshots or a sample base, but I can’t share the actual data.
What I have tried
I feel like I am dancing around the solution. Tried several filters and such. I made Table 3 and linked the course records with cross list codes to it. It has them all grouped as you would expect with a link. I then did a rollup of those records to return the lowest unique course number. But I can’t seem to return just one of them back to table 2.
Aug 25, 2022 10:47 AM
Does that mean you have a column in Table with one of the following:
You will have the easiest time if either 4 or 1 apply. If 4 matches your use case, you could just use that field to link to Table 2. Since multiple records from Table 1 will be tied to the same code in Table 2, those cross listed courses will only show up once in Table 2.
Aug 25, 2022 11:15 AM
Good clarifying question.
It’s #2. All courses have the field. It is blank if they are not cross listed and have a unique pairing code (that can span as many as for courses).
I stumbled into a solution, but I think I am taking too many steps or it is inelegant. Mostly cause when I have to reimport the CSV flat file every morning, there are manual steps I have to do to restore links. But, here is how I made it work and will try to tighten it up.
Table 1 (CSV Flat File) => Linked via unique CRN => Table 2 (Records for review)
Table 1 (CSV Flat File) => Linked via cross list code => Table 3 (Crosslist Cleanup)
Table 2 (Records for review) => Linked via unique CRN => Table 3 (Crosslist Cleanup)
On Table 3 I am using a roll up to return the MIN(value) for the linked records from Table 1 which I then manually copy to the field that is linking to Table 2.
On Table 2 I have a lookup from Table 1 that brings over the cross list code and the link from Table 3 for the selected cross list course I want to show. I then made a formula that checks if the lookup and cleanup are blank OR the lookup and cleanup match to return a “yes” value. I think added a condition to the filter to only show the courses that return a “yes” and that appears to work.
Like I said, I feel like I stumbled into this and not a full on “solution”, but I’ll keep playing with it. If I could get automations to play nice making all these manual links, that would be great, so I am desperately trying to just have my tables “react” to data in the flat file import.
Aug 25, 2022 12:09 PM
Automation is probably possible but it is difficult to point what needs to be done without screenshots.