Skip to main content

Hello there,

I am very new to Airtable (just started two days ago) and I am finding that there are so many things that can be done here so I am very eager to learn. So TLDR, me and my friend has a small business where we need to find suppliers and ask for their quotations for bidding purposes. We have been very overwhelmed lately and want to leverage technology to help us automate some of the jobs. I have a problem with configuring the database right and would appreciate any insights from you guys.

 

Working Process:

Currently, our working process is quite redundant and consumes so much time. So first, the purchase request will be decided by us on what items to buy. Then my colleague will print out those items (Row) along with some potential suppliers (columns), then we will work together to pick which supplier we will ask for each item. Also it should be noted that in each box, there may or may not be past price (if we ask them for quotation before). After that, my colleague has to then input those back into the excel and individually make it table for each supplier. Each supplier will send quotation and we basically just print all those quotes and check together. As you can tell, the problem with this process is that we have no past data to rely (or at least, we are limited in terms of how much we can analyse the data as they are not in standard database format). Hence this is the reason why I want to try using Airtable to emulate this bidding process.

 

So I have three tables (Request, Price Comparison and Supplier). Request Table has the following fields:
Items (Primary field) [Single line text]
Reference document [Single line text]
Unit [Single select]

Quantity [Number]

Price [Currency]
Supplier [Single select]

Logistics status [Single select]

Price comparison [Link to Price comparison table]

 

Price comparison Table has the following fields:

Items (Primary field) [Single line text]

Reference document [Single line text]

Unit [Single select]

Quantity [Number]

Price [Currency]

Status [Single select]

Picked [Check box]

Supplier [Link to Supplier table]

Request [Link to Request table]

 

Suppliers Table has the following fields:

Name (Primary field) [Single line text]

Contact Method [Single select]

Country [Single select]

Price Comparison [Link to Price comparison table]

Request [Link to Request table]

 

 

 

Automations that I have done/ want to do:

 

  1. I want it so that after I finished typing out the fields in request table, those same fields will be created in the Price comparison table. This one, I have achieved through automation.
  2. In the Price comparison table, I added a list view and group by Items. The reason why I want to do this is to be able to type out the suppliers for each items so that it can be seen by my colleagues. However, I am unable to achieve this. When I added new records under those Item group, I would have to refill all the values again; or otherwise, only the Supplier field will be filled and the rest of fields in the record will be blanked. I have tried automations, but I am clueless on how to make it work.
  3. If this can be achieved then I want to include another automation process to make it so that when I checked the box for certain suppliers in the Price Comparison table (thus signifying that this supplier is confirmed for this item), the Supplier field in Request table will be updated to reflect that.

 

The fields (except value) in price comparison table are all automated by the entry in Request table. My work flow is that my staff will input the items in the request list. Then I will select which item goes to which suppliers for bidding process. All items will go to multiple suppliers. I want to know if this process can be done purely through Airtable.

at first glance this looks like you'd be able to do all of it in Airtable! Depending on the fields, you could use default values in AT for when you're adding new linked records and/or set up the automation to populate the fields accordingly (e.g. by formula). With group by items you can have the fields copy from the grouped item record. 

You can also set up an automation on price comparison (e.g. when a record is updated - only looking at the checkbox, find the associated request record and update with the checked suppliers' info 

Happy to take a look through or review live if you'd like too 🙂 

Hi, From what I understood, here's what I'd do (I know that's not what you asked, but I think you could do better):

Start with these tables:
Items (a list of unique items. No quantities or pricing to be entered here)
Suppliers - list of supplier names, with contacts etc.
Purchase Request (there will be multiple requests over time) - You'll choose who all to send the request for quote to in this table. Use an automation here.
Purchase Line items: (links to parent Purchase Request and to Items table). For each PR, you'll have n items. Choose the Parent, Choose the Item and add qty.
RFQ: Individual record of which Supplier was sent which Purchase request - Records would be generated by automation or manually AFTER a request is sent to a client. Use Fillout for this. 
RFQ Line Items:  Responses received from Suppliers via Fillout (or something else) will be stored here. Each response will be linked to Its Parent RFQ AND Purchase Line Items record. 

The workflow would include you setting up a RFQ, choosing items and qty for each, > setting the suppliers that you send it to > sending RFQs and reminders if needed > suppliers fill data and automatically shows up in relevant table > run script to perform analysis> Done.

Then since all records are linked properly, you can run a script that runs through each 'Purchase Line items' and compares it to all RFQ Line Items received from the suppliers. You can extract information like avg bid, highest, lowest, median and write it to a relevant field in Purchase Line Items table for comparision. Looking at the linked ITEM will give you ability to compare with any bids in older purchase requests.

I believe if you paste this comment in Airtable AI app builder, it'll get you 60-70% there.

Few key points to note:

  • When you have structured data, you don't need manual comparision etc. Automations can take care of it.
  • Rely on lookups, rollups and automations to get data from one table to another, copying and pasting shouldn't be needed for a good base design.

I do provide consultations for this, if you're looking for more directed support. 
You can use my free ChatGPT Scripting helper to help with building the scripts if needed.

Keep building!

Hi, From what I understood, here's what I'd do (I know that's not what you asked, but I think you could do better):

Start with these tables:
Items (a list of unique items. No quantities or pricing to be entered here)
Suppliers - list of supplier names, with contacts etc.
Purchase Request (there will be multiple requests over time) - You'll choose who all to send the request for quote to in this table. Use an automation here.
Purchase Line items: (links to parent Purchase Request and to Items table). For each PR, you'll have n items. Choose the Parent, Choose the Item and add qty.
RFQ: Individual record of which Supplier was sent which Purchase request - Records would be generated by automation or manually AFTER a request is sent to a client. Use Fillout for this. 
RFQ Line Items:  Responses received from Suppliers via Fillout (or something else) will be stored here. Each response will be linked to Its Parent RFQ AND Purchase Line Items record. 

The workflow would include you setting up a RFQ, choosing items and qty for each, > setting the suppliers that you send it to > sending RFQs and reminders if needed > suppliers fill data and automatically shows up in relevant table > run script to perform analysis> Done.

Then since all records are linked properly, you can run a script that runs through each 'Purchase Line items' and compares it to all RFQ Line Items received from the suppliers. You can extract information like avg bid, highest, lowest, median and write it to a relevant field in Purchase Line Items table for comparision. Looking at the linked ITEM will give you ability to compare with any bids in older purchase requests.

I believe if you paste this comment in Airtable AI app builder, it'll get you 60-70% there.

Few key points to note:

  • When you have structured data, you don't need manual comparision etc. Automations can take care of it.
  • Rely on lookups, rollups and automations to get data from one table to another, copying and pasting shouldn't be needed for a good base design.

I do provide consultations for this, if you're looking for more directed support. 
You can use my free ChatGPT Scripting helper to help with building the scripts if needed.

Keep building!


I just pasted my comment in Airtable Base/app generator and here's the unmodified outcome:
https://airtable.com/appmAyD1SS2i83Ivn/shr4iRhbehkiUlSQU

Hi guys! 

I am very sorry for the late reply as it is Chinese New Year so I did not have the chance to check the posts. I will see all your feedbacks and update accordingly. Really appreciate all your help!

 

Reply


Terms & Conditions