JohanA Posted October 27, 2016 Share Posted October 27, 2016 (edited) Hi all, new user on the block Anyhow, I want to be able to search a table from another table. I have tried using ExecuteSQL, but I seem to not be able to make it so I can for an example type "89547" instead off "895476", using the wildcard %.It only returns ?. In the example file I attached I think I have been able to explain it more clearly. It has 1 000 randomly generated data to test on. It does not however contain any scripts. If anyone could help me get on track I would be very grateful :=) Regards JohanA Example.fmp12 Edited October 27, 2016 by JohanA Link to comment Share on other sites More sharing options...
webko Posted October 27, 2016 Share Posted October 27, 2016 It seems that LIKE doesn't work on number fields - when I changed to Text for Prod_number, this worked: Let([ query = "select\"Prod_Name\" FROM Products WHERE Prod_Number LIKE ?"; result = ExecuteSQL( query ; "" ; ""; "45%") ]; result ) 1 Link to comment Share on other sites More sharing options...
JohanA Posted October 28, 2016 Author Share Posted October 28, 2016 Huge thanks webko, was going nuts on why it did not work Now I have to figure out how to fill the portal, on the video I watched ge used the SetKey if I arent misstaken. Link to comment Share on other sites More sharing options...
JohanA Posted October 28, 2016 Author Share Posted October 28, 2016 Do anyone have a clue on how to output the result from a ExecuteSQL to a portal? When I tried Set Field and have multiply rows of data it got put into one field Link to comment Share on other sites More sharing options...
Wim Decorte Posted October 29, 2016 Share Posted October 29, 2016 If you set the output from the ExecuteSQL (provided that it is just one field in the query) to a global field you can then use that global field as the 'left side' of a relationship to the table you want to show in the portal. Looking at the query earlier in the thread, you'd want to return the product ID then, not the product name to avoid ambiguity in the relationship. Another technique for this is "virtual list", do a search on the forum here and you'll find plenty of examples. Link to comment Share on other sites More sharing options...
JohanA Posted October 29, 2016 Author Share Posted October 29, 2016 I am having trouble showing the result in the portal. I am using Set Field to set the results in. It's a global field and the portal is showing that field and another. The problem is if I get many rows of data, it all gets put in one line. It just shows the first line, all other lines is there but I have to click in the portal for them to show. Any ideas on how to make it show on seperate rows? The Set Field script step looks like this, the variable the data is in is named "te": Set Field [Order::keyh_PORTALFILTER; $$te & ¶] Link to comment Share on other sites More sharing options...
Wim Decorte Posted October 30, 2016 Share Posted October 30, 2016 You're missing the part where you use the global field where you've set the result as the key to a relationship to the products table. The portal will only show fields from that products table, the global results field is not in the portal (and does not even have to be on the layout) Link to comment Share on other sites More sharing options...
JohanA Posted October 30, 2016 Author Share Posted October 30, 2016 After much fiddeling around I managed to get it to work I must have been blind, you sad " The portal will only show fields from that products table " , but stubborn as I was I added the global result field to the portal, which did not work at all Anyhow, huge thanks. Now I can move on and get this solution working. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2862 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now