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.

?

why use a query for this?


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. 


Solution

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



Try this:

  1. Test on run-time
  2. For Test Queries, configure service center and don't forget to set the test value in the advance query with the tenant id.

David Chen wrote:

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





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


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?


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


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

Here it is

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. 

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


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.