showing a list of records in an unrelated table

[EricPSmith]

Hi Folks:

I'm trying to do something that seems simple and straightforward, yet I can't seem to find my way there. As I recall, I used to do this in Access without a problem, but I don't see how to do it in FileMaker.

I have a layout that shows fields from a Main table, as well as a portal containing records from a Child table. Child records can be scrolled, edited, and added; and they are sorted appropriately. This part works fine.

Now I want to add a portal to an Unrelated table, where I can similarly scroll, edit, and add records. I also want to control the sort order, as before. And I want it to always start already scrolled to the last record, with the empty new record row visible.

When I add a portal to the layout, the Portal Setup dialog shows only related tables available in the popup menu, and all unrelated tables are disabled. If my Unrelated table were pickable here, it seems like this whole thing would 'just work.'

As a workaround, if I create an artificial relationship between Main and Unrelated using the 'x' relational operator (is there a name for this operator?), the Edit Relationship dialog disables the option to allow creation of records in the Unrelated table.

The latter approach at least lets me display and sort the Unrelated records, but I can't add new ones, and I don't yet see how to get the portal to always scroll to the last of the existing records. (I've tried plugging in a fixed number for Initial Row in Portal Setup, but that's static, whereas the number of Unrelated records will be changing. )

Does anyone know how to produce this simple behavior?

Thanks in Advance,

Eric

[Genx]

You'll have to add them using the manual method... go to the other layout and add a record and then return it... using a script of course

[Genx]

Alternatively,

You can make a stored calculation in both tables result being 1 and relate this with an equal operator instead --- then allow creation will probably be on.

[Inky Phil]

using the 'x' relational operator (is there a name for this operator?), Eric

This is known as a 'cartesian' join or relationship

Further to Genx reply, if you use the constant fields then you can definitley set creation of records on if it doesn't default to it.

HTH

Phil

[Genx]

It's technically called a "cartesian product" but who's being picky ;)

[Genx]

OMG, Phil I LOVE YOUR SIGNATURE... that's how i feel often hehe

[Inky Phil]

Yes but Eric didn't specifically ask for the 'technical' name.

I was just jargon busting.

Phil

D'you know...there is always someone isn't there? ;)

[Genx]

I'm glad i could be that someone this time -- i mean really, what would life be without someone? Probably empty..

[Inky Phil]

Oh by the way............welcome to the forum Eric!

[Genx]

Oh my, i forgot...

Yes welcome Hope we helped.

"And now for something completely different..." I'm really struggling to get anything done at the moment Lol!

[Inky Phil]

Oh yes, and if you are scripting your navigation to the layout with the portal on you can issue a 'go to portal row (last)' script step

Phil

[EricPSmith]

Well thank you both for the suggestions and the kind welcome. Your feedback has been a terrific help. Sometimes I'm amazed when an issue I've been stuck on for a while magically dissolves with just the right pointer or two.

I implemented your suggestion for relating calculated constant fields and, with a slight modification, the portal works just as I had hoped. Initially, I had the constant field calculated on both the Main and Unrelated tables. This gave me an editable new record row in the portal, but entering data in any field and then leaving the field kept producing an error message saying 'This action cannot be done because the field is not modifiable.' On a hunch, I changed the constant field in the Unrelated table from a calculation to an auto-entered number (=1), and now the portal is working great. It would be nice if FileMaker had the display and modification of unrelated tables built in, but thankfully this solution gets the job done.

For moving to the last record in the portal, I've added Go to Object (PortalName) and Go to Portal Row (Last) to the main script that opens this layout, and that works great too.

The only thing I miss in FileMaker is Access' extensive OnEvent triggers, which allowed me to attach a script to almost any event - entering a field, exiting a field, clicking on a tab, etc. It would be much more natural to attach the Go to Portal Row (Last) command to the tab containing this portal (or better, to the portal itself) so that I'm always at the last record no matter how I get to that portal.

(Right now, if I go to a different Main record or create a new Main record, this portal pops back to its first record. I know I could add custom menu commands that add this script step to record navigation/ new record/ delete record commands, but that's a clumsy way compared to triggering Go to Portal Row (Last) whenever the portal is displayed or activated. If you know of a better way within FileMaker, please do let me know.)

Anyway, it's gratifying to have things working so well, thanks to your help.

Many Thanks,

Eric

P.S. Ahhh, 'Cartesian Product' - I think I vaguely recall learning about these back in school, around the time of matrices and arrays. *That* was a long time ago!

[Genx]

Take a look at this ... It might help

http://fmforums.com/forum/showtopic.php?tid/74452/

Alternitively, you might consider using DoScript (a free plugin) do a google search.

Make an unstored text calculation and put it somewhere out of sight on your layout (make sure you also arrange it to be at back, i.e. click the send to back button -- this means it will load first on the layout -- next write a plain script that says

"

Go To Object[i don't know the syntax here but your portal]

Go To Portal Row[Last]

Commit Records[]

"

calling it... I don't know, "last portal row"

In your calc field that you defined before, simply type: mFMb_DoScript( "last portal row" )

and then you have yay fun -- Everytime you switch records etc. or view a record where that calc field exists on the layout, it will run the script and flick to the last portal row...

However, be careful with this -- it will trigger any time the layout is refreshed -- but you should be fine if you use freeze window in your other scripts that run over this layout.

[EricPSmith]

Genx:

I've downloaded DoScript 2.0 ( http://www.myfmbutler.com/index.lasso?p=416 ), and it looks quite promising. I'm excited by the list of available triggers listed on the website:

• trigger a script on entering a field

• trigger a script on exiting a field

• trigger a script when committing a record

• trigger a script on layout refresh

• trigger a script using a timer

• trigger a script based on FileMaker's Idle mode

The included sample file that shows the triggering you've described, based on layout refresh, works fine and appears to provide what I want. I'm still fiddling with getting it working in my own application - I think the calculation may be evaluating in the wrong context, but I need to experiment some more.

In any case, it seems you've solved yet another one for me.

Many, many thanks,

Eric

[Genx]

The others are great (but have been around for a while). These two are the best IMHO (like i said not that the other don't have their uses -- the below are just newish)

• trigger a script using a timer

• trigger a script based on FileMaker's Idle mode

I've been doing a lot of DoScript advertising really, just to let you know, there are two more free event plug-ins -- zippScript and EventScript.