security filter

[Dafer]

I would like to filter an column in one table on my app. Basically,  I built my app based on store#, the user table has many info about the store such as store#, address, email and so on, each table in my app connect with user table by store# as a key. What I would like to do is when I enter the data in a table then the store # field supposed to  filling immediately  based on the email user. 

[Craig_QREW · Accepted solution]

This should work well for you. You want the store # to fill in automatically because any user is linked to their store. You can set an initial value for the store numbers in the columns section. 

That initial value could look like this: ANY(SELECT(user[store #],USEREMAIL()=[email]))

This will look in your user table for where the email address of the current user matches then it will return that store #. If you put this in initial value it will fill in and be changeable if someone wants to change it. If you put it in formula then it will not be able to be changed. I am not sure what this has to do with security filters but you can write one where people can only see their stores information by using a similar function for those that are not admins. 

Hope this helps, let me know if anything else is needed.

Craig

QREW Technologies

SELECT() 

ANY() 

[Craig_QREW · Accepted solution]

This should work well for you. You want the store # to fill in automatically because any user is linked to their store. You can set an initial value for the store numbers in the columns section. 

That initial value could look like this: ANY(SELECT(user[store #],USEREMAIL()=[email]))

This will look in your user table for where the email address of the current user matches then it will return that store #. If you put this in initial value it will fill in and be changeable if someone wants to change it. If you put it in formula then it will not be able to be changed. I am not sure what this has to do with security filters but you can write one where people can only see their stores information by using a similar function for those that are not admins. 

Hope this helps, let me know if anything else is needed.

Craig

QREW Technologies

SELECT() 

ANY() 

[Dafer]

Thank you so much Craig, that's exactly what I wanted, I appreciated 

[Dafer]

Hey There, 

I would like to pull data from payout table to weekly settlement table ( pay out record)  as initial value, what I did is, I made the pay out record in weekly settlement table reference to payout table to that specific record. 

when I put this formula to pull the data: 

Any(
SELECT(
Pay Outs[Total],
AND(
[Store #]=[_THISROW].[Store #],
[date]=[_THISROW].[date]
        )
    )
)   

I got this error "The expression is valid but its result type 'Price' is not one of the expected types: Ref"!!

I would be appreciate if you help me to do that. 

Thank you

Dafer

[Craig_QREW · Accepted solution]

Good to hear from you again. Looking at your issue I am not sure I fully understand so I will have multiple suggestions. 

1. AppSheet is correct, row number is not a good primary key, normally I set as the first column in any table as the ID and set that as the primary key. (You may need to regenerate structure for it to appear.)
2. So when there is a problem with the column types the easiest thing to do is to change the column type to what it says you need. Looking at your formula It appears to me as though you want to get an output of the total column in the Pay Outs Table. That desire is of type price not ref. Here is a potential explanation: Just because you use information and values from another table does not mean that you are of type reference. In fact, you can reference to another table and still have any column type be used. When a column is of type ref then it is looking for a primary key from some other table to be present. 
3. If you want to provide a reference not prices you can change "Total" to whatever the primary key of the pay outs table is and that will create a reference. 
4. If these are not your solutions then I would like to request images of the columns and tables they live in that are being used. (store #, date, total) As well as where the column you are trying to create exists. Additionally I would request to see the image of your data relationship in the info side menu and the Spec tab.

Personally I think solution 2 is your answer and explanation, change the column type to a price because ref is only needed when you are wanting IDs from another table. 

I hope this helps, please let me know if not and I would be happy to look into this more for you. Also feel free to blur any part of pictures you may need to. I know that is often something that stops people from including images and I understand and am able to work around that. 

Craig 

QREW Technologies

[Dafer]

Thank you so much, yea I got it and now it's working properly