Cartesian Product (Cross Join)

[doltharz]

(Sorry for my poor english, i also use french version of FM so some terms might be incorrectly translated.)

I'm quite used to database stuffes but cartesian product isn't working the way I expect it to work. Maybe you can help me on this one.

In a table TSESSION I store settings, filters, sorting orders, etc. for the current user. I want to use these settings to filter out any table based on TSESSION: value. No big deal!

Let's say I have an TINVOICE table i want to filter out based on settings from TSESSION for THE CURRENT USER.

I have 4 sessions, 20 invoices.

I link TSESSION::ID to TINVOICE::ID with a cross join relation (cartesian product). I EXPECT to get 80 records but only get 20.

KINDA:

SELECT s.*, i.*

FROM TSESSION s, TINVOICE i

What am i doing wrong.

I want 80 records I'll filter out using sessionid to get only 20 records to which session information is added.

KINDA:

SELECT s.*, i.*

FROM TSESSION s, TINVOICE i

WHERE s.USERID='00:c0:b7:43:2e:username:hostname'

Sounds trivial for my brain but...

[comment]

You cannot get 80 records, because there is no table that has 80 records. However, you do get 4 records with 20 RELATED records each.

[doltharz]

So how can i ensure that only information based on the current session is added to TINVOICE output.

I'll set a calculated field in TINVOICE _FILTERED_OUT which will be based on information in TSESSION.

If there's a better way to achieve what i'm trying to do? (Like global variables in a "By Session" fashion)

[comment]

I don't quite understand the question. Is there a field in the Invoices table that determines the session to which the invoice belongs? I'm also not sure what do you mean by "output".

[doltharz]

What i'm trying to do is to store settings (like order and filter) in a way i can access it from ALL tables.

SESSION::Filter_Field = "POS"

SESSION::Filter_Value = 17

SESSION::Sort_Field = "SALESMAN"

SESSION::Sort_Order = 1

I'll then use a script to filter out and order results based on the "_FilteredOut" calculated field in the INVOICE table.

I dont want to use GLOBAL variables till, doing so, all users will have to share SAME settings.

I want settings to be preserved between layouts.

Using Hyperlinks (button), I reorder information in some layout and I don't want to loose stored settings or settings from other users to be applied to MY layouts.

I want "Session based" global variables i can use in script like "Get(ScriptParameter)". SESSION BASED is the most important idea here.

Technically, a session is a user on a station.

I hope you got the idea.

Thanks

[comment]

I hope you got the idea.

Probably not, because I don't see the connection between storing user settings in a table and your original question. If your settings table is related to another table, then the settings will be accessible from that table. However, there is the question of WHICH settings. Using a Carthesian product type of relationship, ALL records will be related - but with a reference like "SESSION::MyField" Filemaker will get the MyField value from the FIRST related record in SESSION. I don't think that's what you want.

Note: I'd prefer if another member more experienced with implementing a users table would step in here. I'm still debating the issue with myself.

[doltharz]

I think you got the idea of what i want. I'm still n00b to FileMaker but cartesian product was clearly pointing toward what I was looking for BUT till it only returns the FIRST record... it became useless.

Note: If FM8 implements global "per connection" variables, we DO have an 8.x (maybe 8.5) legit version of FM and are able to upgrade. We're stuck with 7.x to ensure maximum compatibility with partners, time to go forward with a newer one.

[comment]

Globals and unstored calculation fields can return different results for each user. There is no difference between Filemaker versions regarding this.

One way to access user settings would be to have a an unstored calculation field in each table = Get (AccountName), or a global field that would be populated by the same value on login. Then you can have a relationship to the users table based on matching this field with the AccountName field in the users table.

[bcooney]

Another technique is to add this to a script that runs on Open,

1. Go to Users Layout (based on User TO).

2. Perform Find. Find User Record that matches Get(AccountName)

3. Grab any "session" desired prefs for the user and set them into globals.