Fields not updating through a portal

[Rich S]

Howdy all:

I apologize in advance if this isn't the proper forum for this question since it involves external data sources, ESQL, etc., but the crux of the problem is that none of the fields (in the portal) will sort.

In the attached (original) file, everything works as it should. I've copied it, but instead of using an intra-file table for contacts, the file I created connects to an externally-sourced FileMaker Pro file elsewhere on the server for Contact data.

The lookups for (external) data work fine in the new version--it's only the sorting part of the solution in the Sort layout that doesn't work, so given that everything was faithfully reproduced and coded in the new version, is the reason why the Z_Sort fields aren't updating (they're staying solid as 1's) because I'm using an external data source?

 

Cheers,

Rich

 

 

FM SQLPortal Filter & DynSort copy.fmp12.zip

Edited June 13, 2024 by Rich S
Added relationship graph from "new" file

[comment]

A simplified example would be helpful.

From a casual glance It would seem that the sort order depends on some global variables? The scope of such variables is limited to the current file.

Not sure what this has to do with SQL. Using another FMP file, located on the same server, as an external data source is done natively.

 

 

[Rich S]

Thanks--I think you answered my question: global variables are limited to the current file.

There's some SQL in a few of the scripts--mostly for the look-ups, I think.

 

*sigh* Such a great solution to use for what I need. Time for a Plan B.

[Søren Dyhr]

4 hours ago, Rich S said:

it's only the sorting part of the solution in the Sort layout that doesn't work, so given that everything was faithfully reproduced and coded in the new version, is the reason why the Z_Sort fields aren't updating (they're staying solid as 1's) because I'm using an external data source?

Are you sure? - the template you make references to: https://filemakerhacks.com/2016/12/29/fmsql-portal-filter-dynamic-sort/

Does in my humble opinion, have a less than spreadsheet'ish behaviour - where if you "tickle" a detail here, something changes elsewhere. 

In order to make it work, should the filter be cleared before, the sorted button behaves ... at least to my expectations!

 

--sd

[Rich S]

Me? Sure? Only in death and taxes.

Thank you VERY much for posting the link--I apologize to everyone for not doing so.

 

I agree--it ain't pretty and is spreadsheet ugly--I'll need to "prettify" whatever I end up using since it's going to be accessible through WebDirect...which is the reason why I didn't set up the schema in my target database since it's an in-house one that I'd like to keep secure as possible from the outside world; in my (limited) thinking, it's safer to have users log into a "brainless" file that connects to its data source instead of outsiders having direct access to the data file. (Yes, I know FileMaker has good security protocols, but still...anything to make data safer.)

 

The problem with clearing the filter, before sorting, is that it clears the portal of data...at least in "my" version. I'll keep toying with it (and reading the article again in FileMakerHacks) 'til I give up.

[Søren Dyhr]

Take your issue up with Kevin Frank in “Hacks” 

But from my meager SQL-expirience do I remember ORDER BY … instead of using FileMaker variables. but Monkeybread or Baseelements plug-in’s might have something up in it's sleves here?

Another thing to think of, is that cardwindows now work under Webdirect, and it takes all these flush cache issues ... to another place - Daniel Wood writes:

Quote

the most common reason for these refresh issues is around the predicates used in the

relationship being unstored calculations, globals, or from related tables.

https://www.teamdf.com/blogs/14-for-14-ditch-those-cartesian-joins-use-refresh-portal-instead-maybe

Yet another issue, that comes to mind and it's the use of Lookups, wouldn't the separtion model here suffice?

—sd

Edited June 14, 2024 by Søren Dyhr

[Søren Dyhr]

On 6/13/2024 at 4:16 PM, Rich S said:

it's only the sorting part of the solution in the Sort layout that doesn't work,

The template is approximately 8 years old, and meanwhile have filemaker undergone some changes in behaviour, the current table also can be portalized! 
 

This means we now can utilize native scripted sorting instead.

The changes in the sorting comes in later, when I find the time for it🤔

---------------------------------------------------------------------------------

I have now made a template of an idea for the lack of consequence in the sorting in the template form "Hacks" and which to enter something along the lines of the second template attached to this posting.

 

—sd

 

 

 

FM SQLPortal Filter & DynSort-3 Copy.fmp12.zip

TestofUX.fmp12

Edited June 15, 2024 by Søren Dyhr

[Søren Dyhr]

The template TestofUX, have now undergone some changes to facilitate scaling better!

 

TestofUX.fmp12

Edited June 17, 2024 by Søren Dyhr

[Rich S]

Wow! Thank you VERY much for your help!

[Søren Dyhr]

1 hour ago, Rich S said:

Wow! Thank you VERY much for your help!

I'm planning to merge this UX into the file from Hacks, but other assignments have come across ... so wait a few days for this!

--sd

[Søren Dyhr]

You could get rid of the CF Positionvalues, by transferring more than one value with the scriptparameter by using this calc in the definition of it:

Let(tt = 1; List(tt; GetValue( FieldNames ( "" ; Get ( LayoutName ) ) ;tt)))

So in order to make it work in the "Hacks" template, is the tab-ordre of the fields to sort upon - reciprocated in the ordered counting of the included elements:

TestofUXMInusCF.fmp12

[comment]

20 minutes ago, Søren Dyhr said:

Let(tt = 1; List(tt; GetValue( FieldNames ( "" ; Get ( LayoutName ) ) ;tt)))

Beware: getting the field's name from FieldNames() using a hard-coded index value does not protect you against changes in the field's list due to adding or deleting fields. 

This is what I tried to tell you here.  Moving the same calculation from the script to the script parameter does not change anything. You should be using the GetFieldName() function, whose parameter is a field reference.

 

[Søren Dyhr]

Quote

But the contents of the list can change as you add or remove fields on the layout, while the script parameter remains hard-coded.

Isn't the problem you here point at here - solved by counting the numbers of parameters?:

 

...what else am I missing?

 

7 hours ago, comment said:

getting the field's name from FieldNames() using a hard-coded index value does not protect you against changes in the field's list due to adding or deleting fields.

...addding will change the list too, but if the buttons text changes as well with this, isn't much harm done?

--sd

Edited June 21, 2024 by Søren Dyhr

[comment]

3 hours ago, Søren Dyhr said:

Isn't the problem you here point at here - solved by counting the numbers of parameters?:

No.

Let's take a trivial example: suppose you have these fields on your layout, as returned by FieldNames ( "" ; Get ( LayoutName ) ) :

PrimaryKey 
FirstName 
LastName 
DOB 
Address

Now your button that is supposed to sort by LastName is defined to send a script parameter =

List (
3 ; 
GetValue( FieldNames ( "" ; Get ( LayoutName ) ) ; 3 )
)

Not sure why you need both values (and even less sure why you need the tt variable in your version), but that's not important now. What is important is that if later you decide to remove the PrimaryKey field from the layout, your button will now be sorting by the DOB field.

And it's not just adding or deleting fields that can mess up your method; even moving a field backward or forward on the Z-axis will change its placement in the list returned by FieldNames(). 

 

 

Edited June 21, 2024 by comment

[Søren Dyhr]

2 hours ago, comment said:

your button will now be sorting by the DOB field.

Yes but the naming showed on the button, changes as well - this happens from:

....into this, if you remove the middle column:

2 hours ago, comment said:

if later you decide to remove the PrimaryKey field from the layout

It has never been appearing in the layout anyway, why do you mention this when speaking of a current table layout, it's hardly needed?

--sd

Edited June 21, 2024 by Søren Dyhr

[comment]

8 hours ago, Søren Dyhr said:

....into this, if you remove the middle column:

[shrug] If you're happy with such arrangement ...
 

[Søren Dyhr]

2 hours ago, comment said:

[shrug] If you're happy with such arrangement ...

You still havn't told me the meaning with bringing the primary key into the equation ... why is it important to prevent removal of a field not even showing up at the layout in the first place?

But as such isn't it up to me if the users of this solution could with the blunders made here, what I tried here was to avoid the shenanigans involved with sorting on relational portals. I did suggest to pull the relational approach out of the solution, by using the current table portals instead - which would allow me to sort straight into each relevant field instead... simply because refreshing isn't required!

If the relational is urged to this threads "answer" ... could this be an idea?:

https://blog.gomainspring.com/filemaker-community/sorting-portals-dynamically-using-filemaker

...which certainly also might have it's place ... but here did it seem to be SQL searches into a flat table lacking normalization. Such as give me all called George in Pitsburg, and sort on their Lastnames. And where nobody are likely to reshuffle the order of the cells/colomns, say removing an entire column...

--sd

Edited June 21, 2024 by Søren Dyhr

[comment]

22 minutes ago, Søren Dyhr said:

You still havn't told me the meaning with bringing the primary key into the equation

It's just an example. Any field that is listed in the result of FieldNames() before a field selected for sorting, will cause a "shift" to the left when removed or brought forward. 

And of course, any field sent to the back will cause a "shift" to the right.

 

Edited June 21, 2024 by comment

[Søren Dyhr]

I have now merged the two templates, however will it need some serious "weeding" of unused or missing objects, by a tool as https://www.fmperception.com or https://www.beezwax.net/products/inspectorpro-7

--sd

FM SQLPortal Filter Current table.fmp12.zip

Edited June 23, 2024 by Søren Dyhr