Skip to main content

Hide records that have a matching value

  • August 25, 2022
  • 3 replies
  • 54 views
U

I’ll do my best to describe this.

Description
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.

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

Does that mean you have a column in Table with one of the following:

  1. Empty unless the course is cross listed. All courses cross listed with each other show the same code in this field. I.e. “BIO100” and “CHEM100” would both show “X123” in this field, “BIO200” and “CHEM200” would both show “Y456”.
  2. Empty unless the course is cross listed. All courses cross listed with each other show different code in this field.
  3. All records have a value in this field, each code is unique
  4. All records have a value in this field, each code is unique unless the course is cross listed. Cross listed courses have the same code.

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.

U
  • ucmavnerd
  • Author
  • Known Participant
  • August 25, 2022

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.

Kamille_Parks11
Forum|alt.badge.img+27

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.


Automation is probably possible but it is difficult to point what needs to be done without screenshots.

Terms & ConditionsAccessibility statement