If I understood your question correctly, SEC_VALUE1 and PRODUCT_ATTRIBUTE01_ID contain the same type of values (so that matching is appropriate)? If that's the case, instead of using a macro, have you tried doing this with an IF statement in your expressions? I think with the kind of calculations you need you can do it and not even lose much on performance. So it would look like this:
if(PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1, calculate formula)
Thanks for your answer.
The only thing I want to do is select the Products with PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1 if SEC_VALUE1 is not equal to '*', in that case all Products should be shown.
By selecting Products for a specific SEC_VALUE1 it would be best if the non-selected ones are hidden/reduced for that user.
I might be stupid, but I don't really understand your offered solution. But I see that I wasn't so clear with my question either ;-)
Let me try to explain it better in that case.
I've a USRS-table with users using section access and nt-authentication to set up the security.
Per User I've 2 fields (CUSTOMER_ATTRIBUTE04_ID and SEC_VALUE1) that can have either * (a star) or an ID that should select rows from the fact-table for:
a) CUSTOMERS included in the group with ID = CUSTOMER_ATTRIBUTE04_ID (or all customers if ID = *)
b) PRODUCTS included in the product-group with ID = SEC_VALUE1
The a) case is solved automatically by normal QV-links betweem fields with same names in different tables.
Case b) cannot be solved that way because of "circular references for links" (or whatever, see picture in my first post)
Therefore I tried to solve this problem with a macro reading the value for SEC_VALUE1 for the logged on user, but I was unable to do that, due to my lack of experience in both macro and/or VBScript.
The best solution would be not using macros at all, but I cannot get the databasemodel to work (be loaded) into QV the way I want it to, and therefore I tried the macro approach.
So, my main problem is easy, how to read the value of the field SEC_VALUE1 found in the USRS-table for the logged on user using macro?
Hope that makes my problem more understandable without messing everything up ;-)
OK, I think I understand now why you can't (or shouldn't) do this with IF statements. For something like this you would probably need a nested IF, which would crank up the memory required. I think this should work to get the current Windows identity in VBScript:
Set objNetwork = CreateObject("WScript.Network")
strUserName = objNetwork.UserName
Then just compare this field to your SEC_VALUE1. Hope this helps.
Find out the user shouldn't be hard, I can use the function OSUser to do that.
My problem is to select the right SEC_VALUE1 from the table USRS.
My code looks like this right now (remember that I'm a newbee on macros ;-)
Set q = ActiveDocument
Set secval = q.Fields("SEC_VALUE1").GetPossibleValues
If secval.count=0 then
sval = secval.Item(0).Text
Set lb = ActiveDocument.GetSheetObject("lbAO")
Set cp = lb.GetProperties
if cp.Layout.HideExcluded = true then
cp.Layout.HideExcluded = false
if secval = "ResNF" then
cp.Layout.HideExcluded = true
If secval = "ResFG" then
cp.Layout.HideExcluded = true
It is the code for getting the right value to the variable secval that gives me problem.
How can I "filter" the values found in USRS to just give me the value for the logged on user?
Section access uses the NTSID value to get the right user, and that value can also be found in the USRS-table. But I haven't found a way to get that value so that I can use it to select the corresponding user in the USRS-table, and that way get the right SEC_VALUE1...