HI, I build an app to insert order but i would like to have a limit to insert new products, i tried to insert in BEHAVIOR this rule COUNT(Products[Id Products],([User] = [Id Products].[User])) >= 0 but the rule is not correct. How can i resolve it?
Are you trying to count this in a same table or in another one?
in this tableโฆin reality I would need a rule that counts the number of products inserted and the number of products that can be purchased โฆiโve another table โCONTRACTSโ; in โCONTRACTSโ table iโve the the maximum number of products that can be purchased; in โPRODUCTSโ table i inserted a virtual column with LOOKUP the maximum products that can bu purchased
Do you want to just check the quantity of products that can be purschased or do you need to deduct existing orders from this table?
i need a counterโฆfrom maximum to zero. I tried also to insert NOT <0, with a virtual column to count the inserted products, but the action works because the counter also counts below zero (-1)
Not 100% sure but probablyโฆ
LOOKUP([_THISROW].[Id Products],โConcratsโ,โId Productsโ,โMaximumโ) - SUM(SELECT(Products[Quantity],[Id Products]=[_THISROW].[Id Products])) >= [Quantity]
no, it doesnโt work, i tried to change the forumla:
i added a virtual column to count the inserted products in PRODUCT TABLE
LOOKUP([_THISROW].[Customers],โContractsโ,โCustomersโ,โNยฐ Products Includedโ) - COUNT(SELECT(Products[Nยฐ inserted products],[Customers]=[_THISROW].[Customers])) >= [Nยฐ Inserted products]
If i use this rule in another virtual column (without >= [Nยฐ Inserted products]) the result is 0 , than is correct. I tried also to insert a virtual column"PLUGGABLE" in PRODUCTS TABLE to give me result Y/N if result is zero, and in ADD ACTION the rule [_THISROW].[PLUGGABLE]=Y but it doesnโt works
Maybe iโve to commutate the virtual column in a normal row or iโve to put the virtual column in CONTRACT table. What do you think?
Without knowing your table/column structure itโs litle bit difficult to say what would be the exact formula in your case.
i send you a pic to explain my tables
Hi Aleksiโฆcan you help me with this ticket?
Sorryโฆ I have somehow missed your reply. Let me check this oneโฆ soon.
Is the customer in your Products table a supplier?
What you mean? i try to explain the process: The customers stipulate a contract to put in the app some products, iโve 3 kinds of contract with different quantities the customers could insert; for eg, if the customer stipulate a contract with 20 products, i need the โadd buttonโ disappears when the customer has inserted his quantities. Than, i tried different solution: iโve 3 virtual column, 1st with counter, 2nd lookup how many products have customer in his contract, 3rd subtract and if result is โ0โ give me N โฆi tried to insert different conditions in behavior but all attempts were in vain
Gotcha. So every time when the customer makes an order, they need to add a record into your products table, correct?
exactly, i tried to insert behavior (INSERTABLE = NO), or a more complex search function if the result from zero but do not work(IF (COUNT (FILTER( โฆ=0) and also [Insertable Column]<=0 (
this is not correct because the counter give the result -1,-2 etc)
How do you identify the user? Does the customer need to sign in?
yesโฆwith USEREMAIL()
I done also the slices view (ROW FILTER CONDITION: USEREMAIL()=[USER])
Does your products and Contracts table contain customerโs email address?
If they do, you could use the formula with the system generated โAddโ action button.
COUNT(Products[ID Products],[User]=USEREMAIL()))<LOOKUP(USEREMAIL(),โContractsโ,โUserโ,โNumber of products includedโ)
something is wrongโฆ
1st message parentheses i tried COUNT(Products[ID Products],[User]=USEREMAIL())<LOOKUP(USEREMAIL(),โContractsโ,โUserโ,โNumber of products includedโ)โฆmessage COUNT function is used incorrectly
You should never type a formula from a meetingโฆ
COUNT(SELECT(Products[ID Products],[User]=USEREMAIL()))<LOOKUP(USEREMAIL(),โContractsโ,โUserโ,โNumber of products includedโ)
@Aleksi Tx Aleksi, i modified the rule and now it works, but now iโve another problem and I make you a summary:
Iโve a table for PRODUCTS, a table for SERIES, my customers have limitation for PRODUCTS, and SERIES; iโve anoter table CONTRACTS where i put the limitation (Nยฐ OF INCLUDED PRODUCTS, Nยฐ OF SERIES INCLUDED);
PRODUCTS TABLE:
1.ID PRODUCTS
2.PRODUCT NAME
3.SERIES (REF TABLE SERIES with A PART OF)
โฆ
CONTRACT TABLE:
1.ID CONTRACT
2. Nยฐ PRODUCTS INCLUDED
3. Nยฐ SERIES INCLUDED
4. USER
โฆ
i inserted the formula in ADD ACTION for PRODUCT TABLE (COUNT(SELECT(Products[Id Product],[User]=USEREMAIL()))<LOOKUP(USEREMAIL(),โContracts,โUserโ,โNยฐ products includedโ) and it works;
I inserted the formula in ADD ACTION for SERIES TABLE (COUNT(SELECT(Series[Id Series],[User]=USEREMAIL()))<LOOKUP(USEREMAIL(),โContractsโ,โUserโ,โNยฐ series includedโ) and it works in SERIES VIEW, but when i use add with ref column, the rule doesnโt work. How can i Resolve it?
May I ask where this formula actually is? In actionโs โOnly if this condition is trueโ option?
exactly, in the action generate by system
i also tried to insert this rule in tables but it doesnโt work
If I understand your request where you want to remove the โNewโ button while filling the form, you need to do that in table level. You can do that with the โAre updates allowed?โ option if you add a formula there.
@Aleksi, i tried to insert the same action formula but it doesnโt work, can you help me with this formula?
i also inserted a virtual column INSERTABLE Y/N in PRODUCTS TABLE and i added a formula in โARE UPDATE ALLOWED?โ to check [INSERTABOLE]=TRUE in the PRODUCTS TALBLE but also that doesnโt work
@Mark_Pathfinder What is your account ID and table name if I check your app?
@Aleksi id 592176 app AUMMENTHA ARCHITECT table PRODOTTI ARCHITECT
Hi @aleksi did u check it?
@Aleksi hi, can i have a solution for that?
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |