PortalsAnywhere

[willieSVQ]

I’m trying to set up my personal Sales Order Solution. It is meant for running (with a Spanish keyboard) on a Windows-OS 10” netbook, or maybe in a future on an iPad in KioskMode. With the help of St. Google,  I’ve been able to do a decent solution until now, but at this moment I’m seriously stuck.  Probably caused by an relationship error or maybe by an inappropriate CF.

 

My problem is related to ‘item searching’.  The items I need to search for, are in at least 4 languages:  English, French, German and Spanish. Some are written in UPPERCASE,  others in Proper Case,  in lowercase and of course WITH accents. BTW clients are Spanish, so with/without accented first names and last names (2 of them).

 

Well, the FM normal search function and the Quick Search are both case and accent insensitive. The ExecuteSQL  isn’t  case sensitive but is accent sensitive. Besides it can not be used if later on I wanna migrate to FM Go, because of the lack of an SQL engine in FM Go. The FM portal filtering isn’t case sensitive but it’s accent sensitive. In conclusion I’m restricted to normal or Quick Search.

 

On the other hand IMHO the best way to get to the information is a portal showing a foundset. It will allow you to use the ‘Perform Find’ and ‘Quick Find’ script steps, use buttons and triggers on the portal.  I use a CF found on the Internet to get the foundset to a portal. Portals are easy to implement, versatile, accessible, compact and tab panel compatible

 

On a 10” display or touch pad with generously dimensioned buttons there is not too much space, so tab panels and portals are the best solution.  Once I’m on the ‘Orders Layout’ I don’t want to leave it.  Playing around with the ‘New Window’ script step is tedious, erractic, anti ergonomic and will make you loose up to 30% of the screen with obsolete title bar, scroll bars and bottom-something bar.

 

In order to illustrate my problem. I set up a small file, attached to the current post. The client list and ítem list are picked up somewhere from the Internet.  It does not represent the real work flow and lacks of maintenance and at least a proper navigation script.  It’s rather clumsy but will demonstrate where I’m stuck.

 

So, back to the problem.

How can I show the portal with the foundset on the ‘Orders’  layout?, and by extension on ANY layout?

 

Hope there’s someone out there who can help me.

Thank you and greetings  from Seville (Spain)

 

Filemaker Pro Advanced 12v04

Updated Windows XP SP3

_PortalProjectKiosk.zip

[comment]

the lack of an SQL engine in FM Go

AFAIK, the ExecuteSQL function does work in FM Go. I can't test this, so if you or anyone else know different, please let me know. That's not to say that ExecuteSQL is a good alternative to Filemaker's native search - it's not.

 

The FM portal filtering isn’t case sensitive but it’s accent sensitive.

Not sure what you mean by that. Portal filtering as such is neither case/accent sensitive nor insensitive. It all depends on the expression you are using to filter the portal. That's not to say that portal filtering is a good alternative to Filemaker's native search - it's not.

I'm not even convinced that using a portal to show the found set is such a good idea - but if you prefer it that way, see if this helps:

http://fmforums.com/forum/topic/89857-portal-showing-found-set/?view=findpost&p=412279

 

[willieSVQ]

Thanks for viewing my post

AFAIK, the ExecuteSQL function does work in FM Go. I can't test this, so if you or anyone else know different, please let me know. That's not to say that ExecuteSQL is a good alternative to Filermaker's native search - it's not.
 

 

Here is what filemaker.com says about:

http://www.filemaker.com/12help/html/scripts_ref2.37.69.html

[comment]

You see, there's a reason why I emphasized the word "function". You are speaking of the Execute SQL script step, not the ExecuteSQL function. Two entirely different things. You should be referring to the FileMaker Go 12 Development Guide, Chapter 2: Differences between FileMaker Go and FileMaker Pro.

[willieSVQ]

AFAIK, the ExecuteSQL function does work in FM Go. I can't test this, so if you or anyone else know different, please let me know. That's not to say that ExecuteSQL is a good alternative to Filemaker's native search - it's not.

Not sure what you mean by that. Portal filtering as such is neither case/accent sensitive nor insensitive. It all depends on the expression you are using to filter the portal. That's not to say that portal filtering is a good alternative to Filemaker's native search - it's not.

 

I apologize, certainly due to my skill level I wasn't aware of the SQL function.

I'll test it, as the GROUP BY and  DISTINCT clauses might be very interesting  for statistic purposes. Until now  I use the export-import technic for this.

 

Regarding the portal filtering, i refer to the filter function in the Portal Setup.  I attach a small file to

show you what I meant. The Names table has some current Spanish names and 'José' or 'Jose' filtering thought PatternCount() will give different results.

 

The links you gave mainly talk about how to get the foundset to a portal, which isn't my primary concern. However setting up the attached file; I had to set up a relationship, which I did adding and

editing manually the match fields. I'll work on this in order to get it done dynamically by some calculation field, which might be the solution to my problem.FilterTest.zip

[comment]

Regarding the portal filtering, i refer to the filter function in the Portal Setup

 

No, actually you refer to the PatternCount() function you are using in order to filter the portal. But you are right, it is sensitive to diacritics and therefore won't work for your purposes. I am not aware of any other text function that would ignore diacritics, except for the comparison operators (= ≠ < > ≤ ≥).

 

 

 

The links you gave mainly talk about how to get the foundset to a portal, which isn't my primary concern.

 

I guess I don't understand your original question after all.

 

 

So, back to the problem.

How can I show the portal with the foundset on the ‘Orders’  layout?

[willieSVQ]

The original attached file 'PortalProjectKiosk' has 5 tables, a number of layouts and a number of portals scattered around.

All the portals should show foundsets from either the clients- or the products table.

That's what they do perfectly on those layouts showing records from their 'own' tables. i.e. The portal on the Cients Layout shows the clients foundset, etc.

 

On the 'Intro' Layout there is a portal that should show the 'product foundset'. However if you do the search on this layout, nothing will show up.  But.....,  if you change immediately afterwards to the 'Product Layout' you will find the correct result there.

 

So the original question:

                                     ***** What to do to have the same PORTALS working ANYWHERE ?******* ,

 

either through relationships, match fields, join tables or any other technic and of course primarily  on the 'Orders Layout'  where it should allow me searching and selecting without leaving it.

 

10:30 am -immaculate blue sky - 22 ºC (72´ºF)

[comment]

Let me start with two things you CANNOT do:

1. You cannot establish a found set of a table without going to a layout of that table;

2. You cannot refer to a found set of a table from a context of another table.

Keep in mind that relationships work at the data level. There are no found sets at the data level. A found set is a property of the window. You can have a different found set (of the same table occurrence) in each open window - and there is no mechanism at the data level to tell them apart. There is no function that would return 1 if the record is in the found set and 0 otherwise. That's something that just doesn't exist at the data level.


Therefore, in order to show a found set in a portal* you must do these things:

1. Go to a layout of the target table;
2. Perform a find;
3. Collect the IDs of the found set;
4. Set a field in the source table to the collected IDs;
5. Have a relationship between the two tables, based on matching the collected IDs in the source table to the original IDs in the target table.

Some minor variations on the procedure are possible; for example, you could collect the actual data of the target table into a variable, then use a "virtual target table" to display it. You could also use a "global" source table and link all your "real" tables to it using the x operator - that would eliminate the need to "distribute" the found set IDs among all the "real" tables. However, the basic constraints cannot be changed.

 

 

---

(*) Of course, it's not really possible to "show a found set in a portal"; you must first turn the found set into a related set - and that is what the portal will show.

[Rick Whitelaw]

A portal is a way of viewing related records from a different table. How then can you have a portal of Client records on the Client Layout. Assuming that both Client records and layout refer to the same table.