Help

Formula for Number of times vendor is use

Topic Labels: Data Formulas
508 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AmandaC1121
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I am trying to use a formula for reach of our vendors on how many times a request has been sent to them by file number. I have them group by state so it show all the file numbers that went to that state. What I need it for vendor x to show that 70 request have been sent out because file number xwz have been order.

Example

Vendor X is in AZ, TX, GA and FL

Vendor A is in TX, GA

Vendor B is in FL 

We have file 01 in GA, 02 in TX, 03 in GA, 04 in AZ, 05 in TX, 06 in FL 

So Vendor X would have 6 orders, A would have 4 orders and B would have 1 order

2 Replies 2
Jason_Hill
6 - Interface Innovator
6 - Interface Innovator

If I understand you correctly, I would use two tables. The first table is like you suggested in your request with one small change. The vendor in each record (Vendor X, A, or B) is actually linked from the second table. On the second table, you have a row for each vendor, and you could simply use the "Count" column type to count the linked records. To be more advanced, you can use "rollups" to filter different values for each Vendor to gain more granularity. I hope that helps, but again, it depends if I understand your request.

I think you're going to need 3 tables for this:
1. Vendors
2. Files
3. States

And "States" would have a linked field to "Vendors" and "Files"

You'd then create a "Count" field inside "States" to count the number of Files records are linked to that State record, and then you'd create a "Rollup" field inside "Vendors" to sum up the number of Files per Vendor

Screenshot 2023-04-12 at 3.28.49 PM.png

Screenshot 2023-04-12 at 3.28.51 PM.png

 

Screenshot 2023-04-12 at 3.28.53 PM.png

Link to base