Search script and ExecuteSQL

[JohanA]

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

[webko]

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
)

 

[JohanA]

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.

[JohanA]

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
 

[Wim Decorte]

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.

[JohanA]

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 & ¶]

[Wim Decorte]

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)

[JohanA]

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.