I’ve created a CRM app for my company and in order to track the various products a customer uses, both my companies and competitors, I have a form to complete to key the relevant products against the customer.
I have a Products sheet with columns for Product Code > Description > Price etc. Within the form there is an option to select (choice component linked to the Products sheet) our products by description or in the case of competitor products, manual entry.
What I would like to know is, for example, when I choose a product (in this case description) how I can auto populate the product code. (See attachment). Note :: The form writes to a Customer Products sheet. I’m a little lost as to the component and column type I need it to be.
You know, I would probably do this a little differently.
As I understand it, you have a Customer Products table that contains one row per product, per customer. You’re going to wind up with an awful lot of rows in that table, with lots of redundant data.
What I think I would do instead is have just a single column in my Customers table that would contain a joined list of Product ID’s. So instead of adding a bunch of new rows for each Customer, you just use a multi-select choice component that updates a single column in the existing Customer row. You could then relate that back to your Products table by using a combination of split text plus multiple relation columns.
For this approach to work, you would need to add competitor products to your existing Products table. So in effect it would sometimes be a two step process, where step 1 is to add any missing competitor products, and step 2 is to then associate all relevant products with the customer.
Hi
Thank you for your reply, apologies for my late reply, I’ve been away. I think I understand what your saying but how do I select a product and have it auto populate other fields for display eg Selection the description but auto populate a text field such as product code and price?
cheers
You’ll probably need to switch to a Custom Form. Then you can have your choice write to a User Specific column. You can then use that value to create a relation to your Products table, then use Lookup columns to obtain the values that you need.
I’m struggling a bit. I have a company which uses our products and competitors products. The products are being pulled from a products sheet and writing to a customer products sheet. The form is executed from the Company page. All is working ok except that I can’t select the product by description and have it auto populate text fields within the form for product code and price ie the other wanted columns of the row within the sheet. I have to manually enter those, if known. I was hoping I could have a text component which I could point to the column in that row and populates it.
Note:: I have created teams so that more than 1 user can see the companies and add data eg managers.
cheers
Did you try my custom form suggestion?
The problem with the native form is that no values are written to the table until after the form is submitted, so there is no way to build the relation that you need in order to get at the associated product values.
Sorry I sent the reply before seeing this. I understand the logic, I’m struggling to get my head around the actions. I do this with customer equipment as well but I manually enter the required fields. Although it works well for me as I know the naming conventions but if other users were onboarded we would have a situation where the same model was used at different customers but people would name them slightly different or misspell them. Right now I have things related so I can lookup equipment and see which customers have it as well as relate consumables with the equipment they are used on and which customers, all interrelated.
We are a B2B supplier to the manufacturing industry (Commercial Printing). We supply :
• Equipment - New and Used
• Consumables.
Our consumables can be used on some of our competitors equipment and visa versa.
What I have done is create a CRM which has company / contact details as well as the ability to add ours and competitors equipment and consumables at companies that both trade and don’t trade with us. This way I can create charts to measure market share as well as lookup what equipment they have, it’s age and prices paid (if known). I can also lookup equipment to see what consumables others use with that equipment. This is particularly handy when talking with prospective customers. Within my consumables sheet I have created an array to relate the machines the consumables are used with (where that’s the case) and within the machines sheet a column that relates back as well. Not every consumable item is machine specific but some are because of size etc.
Within this I have created a consumables and machine catalogue which I can lookup when at a customers place or on the road if I get an enquiry. This is the reason I haven’t added competitors products to the sheets as they’re our catalogue as well. Maybe I need to create a competitive products sheet to use as a choice
There are other things I have but they’re more utilities and not a problem or customer specific.
Okay. I think I’d still be inclined to keep all products in a single table as much as possible.
It should be easy enough to include a company identifier on each row, and then use that as a filter where ever necessary. The problem with splitting similar types of records across multiple tables is that you end up creating a lot of extra work for yourself.
If you’d like some specific advice or to talk through some of the issues you’re facing, feel free to PM me. We’re in similar time zones, so it should easy enough to set up a call (I won’t charge you anything for an initial call).
Thanks, I might do that. I could then share the app with you so you can see what I’m doing. I work for the company but am doing this as a PrivatePro for myself and will share with management to see if the company see’s any value afterward.
cheers
When it comes to equipment I have an Image carousel to take photo’s of the equipment installed as well.
I’ve also got a Stocklist section which lists all equipment in stock at different warehouses with an Image Carousel and a button to email the details. The objective was to be able to have individuals take photo’s of the equipment in the warehouses where it’s located and upload them to the stocklist. Each rep would then see what was available instead of ringing around and asking for pics. If it was in my warehouse I take the photo and upload it. If it was in an interstate warehouse someone there could take the pics and upload them. If it became sold you just tick sold and it would filter out so nobody had to run around chasing details. The email button had a web hook to be able to email pics and details to anyone who enquired about the equipment. It’s since been rendered useless as I need to update it since Integromat became Make.
