Jump to content

Search script and ExecuteSQL


This topic is 2731 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by JohanA
Link to comment
Share on other sites

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
)

 

  • Like 1
Link to comment
Share on other sites

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

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

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

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 :P
Anyhow, huge thanks. Now I can move on and get this solution working.

Link to comment
Share on other sites

This topic is 2731 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.