Skip to main content
Solved

Design assistance - mapping users to products

  • July 23, 2024
  • 7 replies
  • 60 views
M
Forum|alt.badge.img+11

Hello

I'm looking for some assistance with my base design. I come from a SQLServer background and can do this easy here but not getting the best way to build for AT...

The Scenario
I have a list of users and a list of products.  It's collectibles so I want to be able to know what users have what products, and more importantly which don't they have.

In SQL I'd just create a UserProduct table and pretty much create a record for every user for every product and just record it there (not hugely scalable I know but not a big issue).

In AT I already have the two base tables Users and Products.  I know once I have the base data I can create Got/Not Got views easily but that base table is baffling me - probably over thinking!!!

I'm sure it's easy but I'm just not getting it

TIA
Mike

Best answer by TheTimeSavingCo

Thanks Adam.

That's interesting. I wasn't aware of doing a "without" link.  How do you build that please?  Can't see the field definitions in your example.  I think that is the answer to my question - i want to know who hasn't got Product 1 so I can market it to them

Thanks for sparing the time to help
Mike


Hmm, after creating the records in Users and Products, I created a link between the two and manually linked them together?  So sorry, I think I don't understand what you're asking

===
re: Getting a list of who doesn't have Product 1

Ah yeah, you could use a filter for that and I've updated the base with a view called "No product 1":

===
The system above should solve your problem, but filtering like that also seems slightly tedious.  In these situations I also like to use a checkbox to filter stuff and I've created a view called "Easy product checking" for you to check out if you get bored.  When you mark a checkbox in the 'Products' table, this view in the 'Users' table will display users who don't have the marked product

7 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, I'm not too sure I'm following so I put together something here.  What would be the next step from here?

Adam, Airtable Consultant - thetimesaving.company
M
Forum|alt.badge.img+11
  • Mike_Ball
  • Author
  • Known Participant
  • July 23, 2024

Hmm, I'm not too sure I'm following so I put together something here.  What would be the next step from here?


Thanks for taking the time to answer Adam. I think it's on the right lines but unfortunately the link doesn't work. If you could check and I'll take a look

Really appreciate your help
Mike

B
Forum|alt.badge.img+11
  • Blake_D
  • Inspiring
  • July 23, 2024

If i understand what you are asking for correctly.

An option could be having 2 linked fields to products. The with and without. An automation can be created to look at a checkbox "Create Without List" where once you have the got products linked it searches for all not owned products and links them to that user which will help with the view. You can also go the route where if product link "got" is updated and the box is checked it updates the "Without" link to exclude that product. 

2 Attachments
M
TheTimeSavingCo
Forum|alt.badge.img+31

Thanks for taking the time to answer Adam. I think it's on the right lines but unfortunately the link doesn't work. If you could check and I'll take a look

Really appreciate your help
Mike


Whoops, sorry about that, have updated the link!

Adam, Airtable Consultant - thetimesaving.company
M
Forum|alt.badge.img+11
  • Mike_Ball
  • Author
  • Known Participant
  • July 24, 2024

Whoops, sorry about that, have updated the link!


Thanks Adam.

That's interesting. I wasn't aware of doing a "without" link.  How do you build that please?  Can't see the field definitions in your example.  I think that is the answer to my question - i want to know who hasn't got Product 1 so I can market it to them

Thanks for sparing the time to help
Mike

TheTimeSavingCo
Forum|alt.badge.img+31

Thanks Adam.

That's interesting. I wasn't aware of doing a "without" link.  How do you build that please?  Can't see the field definitions in your example.  I think that is the answer to my question - i want to know who hasn't got Product 1 so I can market it to them

Thanks for sparing the time to help
Mike


Hmm, after creating the records in Users and Products, I created a link between the two and manually linked them together?  So sorry, I think I don't understand what you're asking

===
re: Getting a list of who doesn't have Product 1

Ah yeah, you could use a filter for that and I've updated the base with a view called "No product 1":

===
The system above should solve your problem, but filtering like that also seems slightly tedious.  In these situations I also like to use a checkbox to filter stuff and I've created a view called "Easy product checking" for you to check out if you get bored.  When you mark a checkbox in the 'Products' table, this view in the 'Users' table will display users who don't have the marked product

Adam, Airtable Consultant - thetimesaving.company
M
Forum|alt.badge.img+11
  • Mike_Ball
  • Author
  • Known Participant
  • July 24, 2024

Hmm, after creating the records in Users and Products, I created a link between the two and manually linked them together?  So sorry, I think I don't understand what you're asking

===
re: Getting a list of who doesn't have Product 1

Ah yeah, you could use a filter for that and I've updated the base with a view called "No product 1":

===
The system above should solve your problem, but filtering like that also seems slightly tedious.  In these situations I also like to use a checkbox to filter stuff and I've created a view called "Easy product checking" for you to check out if you get bored.  When you mark a checkbox in the 'Products' table, this view in the 'Users' table will display users who don't have the marked product


Thanks Adam that makes a lot of sense, bit of lateral thinking compared to SQL I guess 🙂

 

TheTimeSavingCo
Terms & ConditionsAccessibility statement