299
Views
12
Comments
Solved
Multi Tenant advance query

How do you advance query in Muti tenant app

I have like a simple query:

INSERT INTO {Entity1} ([Attribute1],[Attribute2])
SELECT 'ABC123','Demo'

But gave the error below

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

?

Rank: #122
Solution

Hello David,

For the INSERT on advanced queries over multi-tenant entity, you must specify the TENANT_ID:

INSERT INTO {Entity1} (TENANT_ID,[Attribute1],[Attribute2])
SELECT @TenantId, 'ABC123','Demo'

And pass @TenantId as input parameter, setting it value to Site.TenantId. 


mvp_badge
MVP
Rank: #13

why use a query for this?


Rank: #1125

J.: Because those Create/Update/Delete Action aren't enough for what we do. We use a lot of JOINS,Aggregated Functions,GROUP BY, ROWNUMBER etc...

Rui Coutinho: Seems like still producing the same error when I do the test query



Rank: #1125

I dont have that in my menu, im using version 9.0.0.23


Rank: #1125

So I published it. Ran the insert action using a button action. No error, however when i check the entity. No data inserted


Rank: #1125

Here it is

Application2.oml

Rank: #1125

Seems like its working showing up on screen, but do you know why its not showing up here?


Rank: #122

As I mentioned before, you need to configure which tenant you want to use during test queries. On your Service Studio version I don't know how to configure it. 

My recommendation is to download and install the 9.0.1.40. The OML I sent you was published in 9.0.1.40 and you were still able to run it.

Hope this helps.

Rank: #122

I took your oml and it works as expected.

Please find my version, where I simply add a Table Records to see on the same screen the contents of the entity. 

Application2_RC.oml

Rank: #122

Do you want to share the OML so I can have a look?

Rank: #122

I don't have that version installed, only 9.0.1.40, that already has that setting available. You can upgrade your developer studio. If I recall correctly, for your version the tenant set is available in popup for the advanced query, next to the TEST button there should be a small arrow.

In any case, have you test it on runtime?


Rank: #122
Solution

Hello David,

For the INSERT on advanced queries over multi-tenant entity, you must specify the TENANT_ID:

INSERT INTO {Entity1} (TENANT_ID,[Attribute1],[Attribute2])
SELECT @TenantId, 'ABC123','Demo'

And pass @TenantId as input parameter, setting it value to Site.TenantId.