Filter data in a table by column in another table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Let's assume that I am an absolute beginner when it comes to working with reports, because I am. I have a table of IP addresses (192.168.1.1 to 192.168.1.254, for instance) in a text field. Aside from that one text field, there are no other fields in the table (other than the auto-generated ones -- RecordID, etc.)

In another table, I have records of all of the computers in my company. When I set up a new computer, I assign it an IP address from a dropdown (that's linked to the IP address table)

What I need to do is generate a report that shows me the *available* IP addresses. But I can't figure out how to tell QB, "show me a list of IP addresses in the IP Address table that are not assigned to assets in the Assets table."

Any help would be appreciated!
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
No problem.



I assume that you have a relationship where 1 IP address has many computers.  (even though in practise it many be 1 to 1.

Make a summary field on that relationships of the # of computers.

Then your filter for the available IP addresses is where # computers is blank.
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points
Correct - in a perfect world it would be a 1-1 relationship; alas, QB doesn't seem to allow 1-1. Anyway...

The IP Address field in the Assets table links to the IP Address table. There's no link in the IP Address table to # of computers, so I can't filter based on that.

So I set up a table-to-table relationship in the IP Addresses table, linking to the Assets table. Now, each record in the IP Addresses table contains a link to "Assets" and an "Add Asset" button.

Am I going to have to assign an asset to an IP before I can filter? That seems like duplication of effort, seeing as the assets are assigned an IP in the Assets table...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
Can you describe your tables and their relationships?  Which is the one and which is the many?  Do you have a relationship between Assets and IPs.
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points
Many "Assets" belong to one "IP Address" -- the reference field in "Assets" is "Related IP Address" which presents a dropdown of IPs from the "IP Address" table.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
You m not understanding the obstacle or else I'm not understanding the ask.

1 IP has Many Assets
Make a summary field on the ipad table of # of Assets

Now the IP table knows it it's been used.  If the summary is zero it is available.  Just make a report of available IPs and use that on he form in the record picker field.
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points
Okay, we're getting closer! You're understanding what I'm asking -- I just don't understand how to set up the summary field in the IP table.

To summarize:

- Assets has a relationship with IPs (many to one)
- IPs does NOT have a relationship with Assets
- I need to know which IPs are NOT used
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
I am dictating this while I'm driving, so I hope this works. What you were saying really doesn't make any sense. One of us is very confused. You are saying that you are married to your wife and you are in a relationship with your wife but your wife is not in a relationship. That is not possible.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
To create a summary feel, you go to the relationship and click on add summary field.
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points
Ha! Okay, I get you. Also in goofing around with this, I accidentally deleted the relationship, so I've recreated it. NOW... I went into the relationship and added a summary field for # of Assets related to that IP address. Aaaannnnnnd.... they're all "zero".

Looks like I'm going to have to rethink/rebuild this.
Photo of BobSawyerGCM

BobSawyerGCM

  • 0 Points
Got it. I'm going to have to go through and edit each Asset and select the assigned IP address from a second drop-down, but it's working. Thanks for talking me through this!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
OK, I guess I'm still not understanding your relationships, as it should not be necessary to do double data entry, but if you are OK them I am OK.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,208 Points 50k badge 2x thumb
If you did not delete that reference field for related IP address, then just re-use it and the will all instantly link up with no need to re-enter the data.