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:
Networks (linked to Devices)
Devices (linked to Locations, Vendors, Tickets, and Networks)
Vendors (linked to Devices and Vendor Scoring)
Locations (linked to Devices)
Tickets (linked to Devices)
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:
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!
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.