navigation problems with self-join filtered layouts

[Rothko]

I have a database where I need to navigate between layouts without the Status Area. A set of buttons do that nicely. Each button has the Navigation script attached, with the left word of the target layout name as ScriptParameter.

I have two questions, one about sort order, the other about displaying the number of records in the current layout:

1.) Some of the layouts share one table, and are based on a filtered TO.

I.e. the common table is called Designs. One set of layouts (Form and List views) are based on a TO occurrence that shows only records with Status=proposed, the other set of layouts is Status=approved.

In order to filter out the non-relevant records the navigation script does a GTRR when the user toggles between List and Form views. However this loses all sorting previously done by the user. Right now, the navi script retains any found sets the user might have had, so I don't want to filter with a Find. Is there a way to retain the sort order?

[color:blue]# check if user wants to toggle between layouts of same table

# toggle with GTTR ('self-join filter')

If [ Get(ScriptParameter) = "Proposed" and

LeftWords(Get(LayoutName); 1) = Get(ScriptParameter) ]

Go to Related Record [ From table: “Designs”; Using layout: LeftWords(Get(LayoutName); 1)& " " &

Case ( RightWords(Get(LayoutName); 1) = "Detail" ; "List"; RightWords(Get(LayoutName); 1) = "List" ; "Detail") ]

[ Show only related records; Match found set ]

Perform Script [ “Clear Sort Indicator” ]

Adjust Window [ Resize to Fit ]

2.) I need to hide the Status Area, as it displays the total number of records in the table, not the total of records in the filtered TO. The navigation script currently makes a Find for Status=approved whenever navigating into the Approved layout and then writes the found count into a global variable, which is then displayed on the layout. This find takes place in a new, hidden window, so that (again) any found sets the user might have had are retained. However, I'm concerned about speed (what if there are 10 users browsing hundreds or thousands of records?). Also sometimes there's that horrible little flash, both on Mac and PC.

[color:blue]If [ Get(ScriptParameter) = "Proposed" ]

# get found count

Freeze Window

New Window [ Name: "hidden" ]

Adjust Window [ Hide ]

Go to Layout [ “Designs table all fields” (Designs) ]

Enter Find Mode [ ]

Set Field [ Designs::Status; "Proposed" ]

Perform Find [ ]

Set Variable [ $$foundProposed; Value:Get(FoundCount) ]

Close Window [ Name: "hidden"; Current file ]

# then navigate

Go to Layout [ $$_lastview_Proposed ]

Adjust Window [ Resize to Fit ]

Surely there must be a more elegant way. I've tried around with the Sum function, but couldn't get it to work. (two calc fields, one displaying a boolean 1 if type=Approved, second calc doing a sum of all the records that have a 1 in that field). Not sure what went wrong there.

[Søren Dyhr]

Have you considered this approach instead of the windowed method?

http://fmforums.com/forum/showpost.php?post/276529/

--sd

[Rothko]

Hi

I'm not sure I understand what you mean. (or what the sample file has to do with my problem). If I understand correctly Ocean West's solution helps to enter data without having to leave the layout.

I need to navigate between layouts because all these layouts have portals on them (and as we know you can't have a portal within a portal).

If I got this all wrong, please excuse me.

[Søren Dyhr]

If you scrutinize his approach is it the utilization of tabs, that gives the features, this means that there hardly are much difference between switching tab or layout, except the found set is kept as well as the sortorder ... and you can indeed have different portals in each tab, now the navigation which are bound to be scripted could include the change of relational key to get the right portion of data shown in the layout.

Now the tab's as such havn't any connection to the required scripting, so it must be established via a trick:

http://www.fmforums.com/forum/showpost.php?post/225429/

...if it isn't enough to click in a portals line...

--sd

[Rothko]

Oh my, I was hoping I wouldn't have to re-do the whole database.

I suppose I'm OK with losing the sorting.

Any ideas about the Found Count? The technique with SUM should work, right? Does the calc need to be done from the filtered TO or from the original TO?

thanks.

[David Jondreau]

It's not clear to me what you're doing, but a Constrain Found Set would keep the sort order. A GTRR to the same TO but using a layout based on a new TO without any relation a Cartesian would store the current found set.

Edited February 4, 2008 by Guest
Note to self: Don't post first thing in the morning.

[Fenton]

I am also not clear. A couple things. The first is the idea that the list layouts need to based on different TOs. A layout is tied to a particular TO, yes. But the TO has little to do with what records the layout will show. A "filtered" TO still shows all the records, and a Go To Related Record can show its results on any layout of any TO of the target base table. Hence it is usually unnecessary to attach a list view layout to other than the anchor TO (for the TOG of that table). UNLESS you have portals in the list view. You don't explicitly say that, and it's important, as they would be filtered by the TO of the layout.

If a user goes from a list view to a form view and back, they are not going to lose their found set or sort order. So why the concern? Why exactly are you going to so much trouble to control what they see? Couldn't this be handled by Finds?

There is a Get (SortState) function, so you could check if you really need to. Don't know if you knew about that function.

[Søren Dyhr]

Yes provided you just layer the new window on top and then closes it when the dealings with it finishes, then are ...

A GTRR to a new TO with a Cartesian would store the current found set.

Ah! Thats right .... although it isn't a carthesian here:

http://www.sumware.net/robfm/savingfoundsets.php

--sd

[David Jondreau]

It's been an hour and a half since I edited that post. Why did you quote the previous version?

[Søren Dyhr]

Sorry about that, had to do some shopping, prepare meal and a few other things... I then found I havn't send my message yet hours later.

--sd

[David Jondreau]

Okay.

[Rothko]

UNLESS you have portals in the list view. You don't explicitly say that, and it's important, as they would be filtered by the TO of the layout.

Thanks for clarifying. No portals in list view, but in form view, yes.

If a user goes from a list view to a form view and back, they are not going to lose their found set or sort order. So why the concern? Why exactly are you going to so much trouble to control what they see? Couldn't this be handled by Finds?

It's going to be used by several people who are going to have their 'own' sets of records that they usually work with, though not exclusively - they will need to be able to access all records. They shouldn't have to go to great lengths to find the record that they were working on before they went away to another layout. Also I'm concerned about speed if they always get shown all records.

A GTRR to the same TO but using a layout based on a new TO without any relation would store the current found set.

Will check this out, thanks.

I'll need to clean it up a bit and will then post my famous navigation script. :B

[Rothko]

Navi script attached.

navigation_script.pdf

[Søren Dyhr]

By splaying it out on two pages of .pdf, are you making even the keenest reader dyslexic, can't you make a tiny template with the relevant layouts and your apparent working script?

By looking and getting hands on the desired workflow, would I be pretty sure we have established enough context and purpose, to advice you better.

The problem with the .pdf is that we can't really invision the layouts contents, because all abstract scripts actually works the only question is if they do as intended??

--sd

[Rothko]

I thought that's easier to read than just cut & paste as you have the indent... anyway, here's a pared-down clone that should show what I'm trying to do.

It's a database tracking the progress of new designs. They can be proposed, approved and failed. Designers think visually which is why I have these icons.

The problems:

1. I need to navigate between the proposed and approved layouts without losing sort order or the found sub-set. Same for the toggling between list and form view.

2. Also I need to display the number of records in the current found set as well as the total of records.

My hunch is these things are fairly easy but my brain is fried - I've been at this for too long. Grateful for any clarifications. I haven't gotten round to trying out D J's suggestion from above.

N.B. I've taken out most fields. The actual database has 7 tables and 3 join tables and a lot more fields. Contacts is still in there to show that [color:blue]"Record " & Get(RecordNumber) & " of " & Get(FoundCount) and [color:blue]"(" & Get(TotalRecordCount) & " total)" work well for a 'flat' layout.

navi.fp7.zip

Edited February 5, 2008 by Guest
(the brain)

[Søren Dyhr]

I need to navigate between the proposed and approved layouts without losing sort order or the found sub-set

It's like that Barenaked Ladies song "Be My Yoko Ono" the problem here is that relations doesn't act particular well on found sets, and I would suggest you constrain found sets instead, although trick with unrelated TO's still could play a role, I have here used JMO/Shawns tail recursion ... I show this in the first template.

I havn't in my first template done any efforts to sort but, although Ray sorts on unstored values would I say it's way to go:

http://www.nightwing.com.au/FileMaker/demos9/demo904.html

Now there is a relational way of doing it - again utilizing the tail recursion CF found at JMO's page, with much less scripting, there are obvious some tradeoff's with every technique - only it's much easier to sort portals via hidden tabs:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000816

This could be applied to my second template the relational one - reducing the scripting to NILL, or rather a singleline under a button!!

--sd

ConstrainFound.zip

TabbedSelfjoin.zip

[Rothko]

Thanks for that. I think I have solved the problem of filtering out records of the wrong type on navigation:

- two unstored calc fields:

'yesifapproved' [Case(Status = "approved";1)]

'yesifnotapproved' [Case(Status = "approved";1)]

-two summary fields:

'totalofapprovedinfoundset' [summary of yesifapproved]

'totalofnotapprovedinfoundset' [summary of yesifnotapproved]

The navi script makes a simple

[color:blue]'go to layout'

If [ proposedlayout::numberofapprovedsincurrentset ≥ 1 ]

Constrain Found Set [ Specified Find Requests: Omit Records; Criteria: Designs::Status: “approved” ] [ Restore ]

End If

This filters out wrong types of records, but even then keeps sort order, and keeps the found set.

As for my other problem, I'm simply not displaying the total of records of a certain type. :B

[Søren Dyhr]

It won't improve to agility of the thing, the more I think about it the more would I fancy the TabbedSelfjoin since the comprehensive scripting is an artifact of loosely relational structured solutions similar could the use of global fields and repeating fields take some kicking as a less than optimal way of solving matters, and when you actually can constrain via the relations, provided a CF is involved ... the second solution did actually arrive to when I was preparing the ConstrainFound for the upload.

--sd

[mkruter]

There is a Get (SortState) function, so you could check if you really need to. Don't know if you knew about that function.

I was actually trying to use that function today. I have a field ("sorted") in a table ("table1") which is a calculation using the get(sortstate) function. The result should be 0, 1 or 2. The field has been placed on a layout using "table1" as its base. When I sort using "mac key-S" the status area displays "Sorted" but the field displays "0" when I believe it should show "1". What am I doing wrong?

Thanks,

Marc

[Søren Dyhr]

Make it unstored, all status functions can't be stored!

--sd

[Fenton]

I was thinking you'd use it in a script.

[Søren Dyhr]

So would I, but the reason for not getting the value, must be a stored field ... wouldn't it?

--sd

[mkruter]

Yes, thats it, thank you!