I`m an proud Seatable cloud user since two or three months and I’m realley impressed of this piece of software. Nethertheless I’m stuck in what I want to achieve with it and would really apreciate any user support.
This is my starting point:
I have a price list with around 400 items. The articles are sorted into 3 categories: A, B, and C. Depending on the category, there are different article and material numbers for the same article.
There are different prices for each item, depending on the number of employees at the potential customer. This means that each customer is assigned to a price category. Depending on the price category, each item has a different price.
I would like to use Seatable to create a price calculation for the customer in which he can see:
the items he wants/needs
with the right prices for him
My wish would be
that I open a new spreadsheet for each customer calculation
define the number of employees
then search for the article name or article number using a search function (and all other information about the article is also displayed/taken over in the new spreadsheet)
the prices of the correct price group are displayed
Is this possible with the help of SeaGable and how do I implement this? Many thanks for any ideas.
As to 1.) Item = article.
As to 2.) Your answer created more questions than it answered. Before your answer, I thought an article/item could have up to 3 prices. Now I hear up to 18?! Also: What’s a price group? Is there any difference to price? Could you illustrate this by making an example?
Hope that clarifys the situation? The only annotation I have to make is, that technically module 1, 2, and 3 are always the same - even if they have differtent article numbers. The difference is the license model. Does that help?
Even if the situation is pretty clear now, your problem sounds pretty challenging to me, particularly the “search/query article and display correct price” part, because I don’t really see which SeaTable feature can answer this…
For the first part, here are my thoughts:
First, you’ll probably need a Customer table containing, for each customer, the needed contact info (name, email, whatever), the license model (single select or better, link to other record from a license model table, see bellow) and the price group model (single select or better, link to other record from a price group table, see bellow)
Second, ideally, you would be able to create a formula to define the price of each product depending on the license model and the price group with ratios or additions between each category, for example:
license model: base price for A, price(B) = price(A)*(1+N%) or price(A) + N and same for C (also defined in relation to the base price from A, each license model other than A having a particular value for N)
price group: base price for pg01, similarly to license model, price(pg02) = price(pg01)*(1+M%) or price(pg01) + M and same for the other price groups (also defined in relation to the base price from pg01, each price group other than pg01 having a particular value for M)
if you do so, the easier for further modifications in my humble opinion will be the following base structure:
a License model table (each line containing a name (for example A) and a value (a ratio to multiply or a single value to add from base price)
a Price groups model table (each line containing a name (for example pg01), a value (a ratio to multiply or a single value to add from base price) and eventually a max number of team members so the system will be able to put any company directly in the right price group depending on it’s number of team members
a Products table containing the description (blue), the name (module 1) and the base article number (001)
if it’s not possible to create such formulas for this second point, I don’t see any other solution than creating a table filled with around 21600 lines (~400 items * 3 license model * 18 price group) with, on each line the description (blue), the name (module 1), the base article number (001), the price group (single select) and the license model (license model). Doing so, you’ll be able to recreate the displayed article number as on your screenshot (something like {License model}&"-"&{Base article number} to recreate A-001 from License model A and base article number 001)
for the last query/search/display part, as I said, I don’t really see which integrated feature could match your needs…
one solution can be the creation of a Universal app with a table page could match the autopopulate request from the search field ( on the upper right corner) but you will still need to create one App per License model + price group (with preset filters for a particular license model and a particular price group), so potentially 54 different apps (still )
another solution I already tested is to create your own html page (you’ll have to be able to host it somewhere) that can interact with a server side mechanism able to return to the client side the right data:
If you’re in ideal situation for second part: the list of products with either base price + both license model’s and price group’s ratios (the final prices will be calculated on the client side) or directly the right calculated prices depending on the license model and the price group
If you’re not in ideal situation for second part: the filtered list of products (only the products corresponding to the right license model and price group)
Doing so, you’ll also be able to create your own search/query field with autopopulate feature
If you’re interested in this solution of creating your own html page, I can give you some more information of what I did or quote you a price for developing such a solution.
Bests,
Benjamin
1 Like
Do it like thousands of other people who have used SeaTable to develop powerful processes and get their ideas and tasks done more efficiently.
. Therefore I created a quick “datamodel” in an unholy Excel sheet:
on the upper right corner) but you will still need to create one App per License model + price group (with preset filters for a particular license model and a particular price group), so potentially 54 different apps (still 