Help with linking and counting tickets

[Alec]

Hi Airtablers,

I’m really stumped here. My base was working great until I needed to add a scoring system where I want to make a few calculations, but the table linking is just not cooperating with me.

I have 6 main tables here:

1. Networks (linked to Devices)
2. Devices (linked to Locations, Vendors, Tickets, and Networks)
3. Vendors (linked to Devices and Vendor Scoring)
4. Locations (linked to Devices)
5. Tickets (linked to Devices)
6. Vendor Scoring (linked to Vendors)

I’m trying to create a scoring system of each Vendor by looking at their total number of tickets and the average time to close/repair a ticket. But I can’t seem to find a way to pull this information via the linking schema I have set up here. I thought about scrapping the Vendor Scoring table altogether and just do the calculations in the Vendors table, but I’ve had no luck there either. I can’t even get the CountA formulas to properly tell me how many tickets each vendor has total (let alone open vs. closed).

See screenshots below:

I don’t know why I can’t get those Open and Closed Ticket lookup fields to be clickable like they are in the Tickets column?

I can’t get the Open and Closed Ticket lookup fields to properly display the tickets that are actually closed and open. I also can’t get that count function right for the life of me.

Do you think it’s an issue that for some of the devices I have more than one vendor linked to them?

Finally, here’s this mess. I wasn’t even sure what to make the primary column so I just had it mirror the linked Vendors. Again, I cannot get the ticket lookup fields to pull the correct data.

Any and all input is welcome here, and if I have to break this all up to get it right - so be it. Thank you for coming to my TED talk :grinning_face_with_sweat:

[Williams_Innova]

Hi @Alec,

I think you are actually really close to having what you want. While I would actually need to spend a little more time with this to be sure, I can offer a few pointers.

1. Can you use the Count field type to count the Tickets field (like the image below)?
   

2. Have you tried setting up Views in your Tickets table to show only Open or only Closed? I think this could help in setting up counts later on. Again, may need to spend more time on this to be sure.

3. It shouldn’t be an issue to have more than one vendor linked. That said, counts could get tricky.

4. I think the same primary field in Vendor Scoring and Vendor makes sense.

If you would like more help, feel free to reach out on here or on my site.

Thanks,
Chris

[Alec]

Hey Chris,

Nice to see you again on here! I actually hadn’t tried the count field type, it didn’t quite work the way I originally planned - but it did kick me off in the right direction to figure it all out (for now at least!)

To your second point, I have set up Open and Closed ticket views - but I’ve only been using them for the front reporting that I’m making through Airtable’s “Interfaces.”

To your third point, I agree and I’ve now cut back so each device is attached to just one vendor (at least for the sake of proving this concept in my demo).

Another (more aesthetic than anything) question, is it possible to get rid of these “Nan” field results from calculations?

Thank you again for responses, Chris. I really appreciate your feedback!

[Williams_Innova]

Hi Alec,

Glad the Count field could lead you down a good path. Its amazing how many times “a solution” is really just a seed to the best solution.

The reason I mention views is using them in your linked fields. This may or may not work for your application. The other thing you can do is copy your view filtering logic to any lookup fields used in other tables. When I say copy, I mean simply to repeat the logic. There is no copy/paste for that, lol.

Glad to hear the reduction of links is helping with proofing. A lot of times, the simple solution is best and it can be difficult to keep that in mind or even know where the right balance is between simple and “Oh wow! This does everything I ever wanted and more!”

As for the “NaN” values, I tend to lean towards an “if” statement using either “0” or “blank()”, depending on what is needed.

Really glad I can be of help and thank you for the feedback and responses too. We are all learning everyday.

Chris