Filtering a Many-to-Many browse with PROP:SQL issue

questions
21

Rick, Can you post your current View definition?

22

Correct. The value for that variable could be saved globally at login OR obtained locally in your browse by using (see docs) GetUserName() .

BTW - If your browse does not involve a lot more than the 1:M:1 files, you likely will NOT even need to mess with PROP:Sql.

23

@ Rick_UpperPark Is this what your asking for?
From SQL:
SELECT dbo.Orders.CatalogNumber, dbo.Orders.Compound_Name, dbo.Orders.PurchaseOrderNumber, dbo.Assignment_.AssignedTo
FROM dbo.Orders INNER JOIN
dbo.isassigned ON dbo.Orders.ID_Orders = dbo.isassigned.ORDERS_ID INNER JOIN
dbo.Assignment_ ON dbo.isassigned.ASSIGNMENT_ID = dbo.Assignment_.ID_Assignment
WHERE (dbo.Assignment_.AssignedTo = ‘Rick’)

Browse View in Clarion:
QuickWindow WINDOW(‘Browse the Orders file’),AT(,358,198),FONT(‘Microsoft Sans Serif’,8, |
FONT:regular,CHARSET:DEFAULT),RESIZE,CENTER,GRAY,IMM,MDI,HLP(‘TestBrowseForSQLView’), |
SYSTEM
SHEET,AT(4,4,350,172),USE(?CurrentTab),#ORIG(CurrentTab),#ORDINAL(1)
TAB(’&1) PK_Orders’),USE(?Tab:2),#ORDINAL(2)
LIST,AT(16,32,329,100),USE(?List),FORMAT(‘60L(2)|M~Catalog Number~L(0)@s1’ & |
‘5@242L(2)|M~Compound Name~L(0)@s200@120L(2)|M~Purchase Order Number~L(0)@s30@’), |
FROM(Queue:Browse),IMM,#FIELDS(Vie1:CatalogNumber,Vie1:Compound_Name,Vie1:PurchaseOrderNumber), |
#ORIG(?List),#SEQ(8),#ORDINAL(3)
END
END
BUTTON(’&Close’),AT(252,180,49,14),USE(?Close),LEFT,ICON(‘WACLOSE.ICO’),FLAT,MSG(‘Close Window’), |
TIP(‘Close Window’),#ORIG(?Close),#SEQ(4),#ORDINAL(4)
BUTTON(’&Help’),AT(305,180,49,14),USE(?Help),LEFT,ICON(‘WAHELP.ICO’),FLAT,MSG(‘See Help Window’), |
STD(STD:Help),TIP(‘See Help Window’),#ORIG(?Help),#SEQ(5),#ORDINAL(5)
END
@ totalhip
Yes, The test Window\Browse is using only the 1:M:1. I am using the function GetUserName() in the OpenWindow embed of the browse and assigning it to a global variable Glo:UserName.
This is the definition of the view in the Clarion Data Dictionary:

image
image1827×794 192 KB

I get this when I use no filters in the browse or embeds using the static string ‘Rick’ In this scenario ‘Rick’ is assigned to 4 projects out of ~ 90,000 records, so I know this is working correctly.
image
image1169×555 70.2 KB

Thanks again for all of your help. I appreciate it.

24

Here is the entire definition of the view definition on the back-end:

image
image1335×1076 37.7 KB

25

Change your view definition to this w/o a WHERE clause:

SELECT      dbo.Orders.CatalogNumber
           ,dbo.Orders.Compound_Name
           ,dbo.Orders.PurchaseOrderNumber
           ,dbo.Assignment_.AssignedTo
  FROM      dbo.Orders
 INNER JOIN dbo.isassigned ON dbo.Orders.ID_Orders          = dbo.isassigned.ORDERS_ID
 INNER JOIN dbo.Assignment_ ON dbo.isassigned.ASSIGNMENT_ID = dbo.Assignment_.ID_Assignment

Import the view into your dictionary to add it as a table from the Clarion point of view.
Then in your browse window, set your filter.
BRW1.SetFilter(‘NewPrefix:AssignedTo = <39>’ & ‘Rick’ & ‘<39>’)

Substitute BRW1 with whatever your browse class name is and substitute ‘Rick’ with the variable containing the name you want to filter on.

1 Like
26

@Rick_UpperPark, Works beautifully! I now see where my thinking was off too.

Thanks Everyone!

1 Like