We are a translation agency and are currently developing a project management system. I use a pricing table that applies to all customers. However, if a customer has a different price for a certain language direction, I don’t want to use a separate price table just for this case. Because the general table contains, say, 150 prices and it may be that a customer only has a different price for a single language direction. Otherwise one ends up with dozens of pricing tables. This problem would multiply because we offer this system for multiple tenants. The simplest solution was therefore to enter the customer ID in a separate column (“X”) if a customer has a different price. And here is my issue:
When I retrieve the price for a specific language direction, (in the simplest case) two rows are returned: a row without customer ID and a row containing the customer ID in X. However, only the following would be correct:
If there is no ID for the customer in the X column, only the general price should be displayed.
If column X contains the ID of the selected customer, only their price should be displayed.
I tried to solve this with a filter plus condition group, but I can’t manage it. Can you help? Thanks!
I think the best way to handle it is to sort by the column X and pagination to 1, so you will get only one result and it will be the one with something in the column X if there is one
Hello Alexis, I’m not sure if this works. If there is nothing in X, the result should be the row without the X. The result with X should only be returned if the client id is in X.
You have a table with multiple rows containing common prices and custom prices
Lang EN price 8$ User null
Lang FR price 8$ User null
Lang EN price 7$ User 123
Lang EN price 7$ User 456
If you get this table with a filter Lang = EN you will indeed get three rows (1, 3 and 4)
If you add the user optional filter User = null OR User = 123 you will get two rows (1 and 4), the common price and the user price
If you add a sort to make the user price first SORT BY User DESC (or ASC I dont remember), you will get two rows (1 and 4) where 4 is first
If you add a limit to 1 row per page and get only the first page, you will get only the row 4, the user price
Then if you filter for another lang, Lang = FR, you will still get the common FR price if the user has no special price
Hi Alexis, this sounds logical. I will try tomorrow. Thank you.
However, there is a weird issue I just noticed:
The field name of column X is customer_id_if_special_rate and in Supabase it’s a normal int4 field. I always wondered why I cannot use = in the filter but now I realized that WeWeb doesn’t treat it as a number (#)!
Anyway, using Is exactly works but I always would like to know why something happens. Any idea why?
That’s probably an issue with how field type detection work. We use the first row to determine field type, but if your column is empty then its null and null in javascript is weirdly typed as an object.
We should probably work on a better way to determine the field type. It would be easy by looking at the table definition concerning supabase, but this logic is common to every data source plugins for now and so we cant have a dedicated way to determine the field type yet. But Its something we have in mind and have to update
I’ve now created a filter which only displays two rows (desc by user X):
But how can I limit the number of rows displayed? If I set the pagination to 1 for the collection, I can never get more than 1 price row (e.g. for editing the whole table). I had another idea: set the height of the list to 1 line. Then the second row will just be hidden. But how?
I’ve now created a paginating_c_rates variable and set it to 0 by default. The collection is using this variable and is also sorted descending. Result: all languages are retrieved which is correct and required. Then I tried setting the pagination to 1 on opening the Edit Task dialog (and reset on saving the changes). But this doesn’t work as expected: There are still two rows displayed. And if the user doesn’t update the task but just opens another menu, the variable will remain 1 which means I cannot use other languages anymore. If, for example, the user changes the language, no price will be retrieved. Similar for Add Task for which other languages should be available for selection. The only solution would be to reduce the height of this table so that only the first row is displayed. But how?
For the time being, I’m displaying the two rows with a note but it’s not really nice_
BTW: If I set the sorting at Collection level, the list in the dialog is still using the wrong order; I have to set sorting again. Bug?
