How to - Automation - Find Owner of equipment and add owner to column in revenue ledger

[mdiesen]

Hello AT community.

Really scratching my head here, maybe someone can help.

Some context, my company offloads data for carriers. We have clients that host access points, basically wifi radios, those access points each bring in a certain amount of revenue.

I have a client table, an access point table (basically inventory), and a Access point revenue table. The Access Point table is linked to the client table so it shows which client owns it. The record ID for the Access point table and the revenue table are the same.

 What I need is an automation that will match the Access Point in the Revenue table to the Access point table to the Access Point in the inventory table. If they match I need it to pull the client that owns the access point and add it to the record in the revenue table. 

Hopefully that makes sense and thank you for the help

[TheTimeSavingCo · Accepted solution]

Hmm, I'd suggest creating a link between the Revenue and Inventory table, that way you could just use a lookup field to display the Client instead.  If you're creating the Revenue records manually, then selecting the AP each time you create the record works

If you're creating those records automatically, then you could try making each AP's unique identifier be the primary value of the Inventory table, and then placing the AP's unique identifier into the linked field to the Inventory table.  This should automatically link stuff up and your Client lookup field will display fine as well

Link to base

[mdiesen]

Thanks for your quick response! Problem with that is its too manual. The revenue ledger is going to be huge.

Also I left out part of the database structure, the APs are also owned by the location. So the order of ownership goes like this 

User -> Client -> Locations -> Access Points -> Revenue Ledger 

Users are for the front end portal, so multiple people can login for a company which is the "Client".
The revenue ledger is manually added every week via a csv. I basically need the Ledger to update all the ownerships automatically when I upload it to the AT Revenue Ledger. So I know which Client is getting how much revenue each week by Location, each locations Revenue is known by the Access Points it owns, and each Access Points revenue is known by the Revenue Ledger. 

Heres how its currently setup

Clients

Locations

Access Points

Revenue Ledger

[TheTimeSavingCo]

Hmm, I'm confused.  The workflow would be:
1. Import Revenue Ledger
2. Click the header of the field that contains the unique Access Point IDs
3. Hit CMD/CTRL + C
4. Click the header of the field that links to the Access Points table
5. Hit CMD/CTRL + V

That would link everything up accordingly and doesn't seem too manual?  If you really wanted to use an automation for it you could too but whether or not that's worth it depends on how often the ledger data gets imported I reckon

[mdiesen]

Ah yes youre right. Thanks!