UNION like VIEW

[doltharz]

Is there a way to reproduce such technique in FileMaker.

SELECT t.Field1 AS Field FROM Table t WHERE t.Field1 IS NOT NULL

UNION ALL SELECT t.Field2 FROM Table t WHERE t.Field2 IS NOT NULL

/*[...]*/

UNION ALL SELECT t.FieldN FROM Table t WHERE t.FieldN IS NOT NULL 

ORDER BY Field



/* If I'm correct, aliases are taken from first subquery and "ORDER BY" clause from the last one.*/

I want to be able to switch from : 

ID, "BMW", "Audi", "GM", NULL, NULL

ID, "Ford", "Audi", NULL, NULL, NULL

ID, "Toyota", "Pontiac", NULL, NULL, NULL

To:

"Audi"

"Audi"

"BMW"

"Ford"

"GMC"

"Pontiac"

"Toyota"

Merci ;)

[mr_vodka]

Are you on FMP7 or do you have an developer copy? There are no UNIONs in FM. What you are asking for can be done I believe with a calc and made easier with custom fuction.

[doltharz]

Can you elaborate a bit more on how it can be achieved?

I think of a N:N join with calculation and filters but there may be an easier way. Without using additional tables and fields.

I don't expect UNION like query to be indexed and/or fast and it's mainly for specific reports where "Other specify: ____________" are dumped and maybe counted.

We've been asked to include more than one of these fields in the same table. Good design would have made this a separate table but it's not the case here.

Thanks

Edited February 25, 2008 by Guest

[comment]

One way to do this is to import the data 5 times into another table (where it should have been in the first place). There may be another, it depends on what you actually need to show or print(?), in what context, and with what other data.

[doltharz]

Using a temporary (volatile) table should also be a solution but it becomes a bit tricky when dealing with concurent users. Is there a way to create a "UNIQUE CONNECTION ID"?

I've tried "MacAddress" but some machines doesn't reveal this information, "Username" isn't safe either.

[comment]

You are using terms that I am not familiar with. If you are asking how to associate imported records with a specific user, I believe auto-entered account name could do this.

You could also build your report as text in a global field - again, your purpose here is not entirely clear.

[doltharz]

The concat approach seems interesting, but I'm not sure the big merged report will correctly span over pages with page header and footer. BTW, I'm not sure at all it will need to.

What i mean by "unique id" is simply a way to know exactly for whom the temporary data are stored there. Kinda "Session ID" on an ASP application. The "Username" is a setting in the client app and can be the same for multiple clients, "Login name" can also be the same for multiple connections till we have role based accounts. Both are useless. I can generate 5 random numbers and expect the sequence to be unique but it's not as safe as the identifier used internally by FileMaker.

[comment]

LOL, you are asking 'how to run after I have shot myself in both feet'. I believe Account Name should be unique. However, you could - if you are so inclined - manage a sessions table, where a new record would be created at login. If you then set a global field or variable with the record's ID, you will have a unique session identifier.

Altogether, it seems easier to fix the data and/or the accounts - although you may find other uses for the sessions table.

[doltharz]

Spreading parameters fields all around the database makes it messy so moving all "parameters" to a "session" table would be the best solution but the way FM works, it's a bit complicated then to access those parameters for calculation in all tables.

To do so, I have to set an extra "constant" field in each table with the only goal to link it to another "constant" field in the "session" table with a [=] relationship. Each table must then have it's own instance of the "session" table to avoid having multiple relationship path between two table objects.

Every day, i realyse how SQL is, compared to FileMaker. Something that requires 2 minutes of coding in SQL, takes 2 days in FileMaker.

[comment]

I have to set an extra "constant" field in each table with the only goal to link it to another "constant" field in the "session" table with a [=] relationship.

Constants are a thing of past - you can establish a Cartesian product relationship by using the x operator with any fields. However, if you pass the data into a global field (or a variable), as I have suggested, it can be accessed from anywhere without a relationship.

Something that requires 2 minutes of coding in SQL, takes 2 days in FileMaker.

Perhaps the opposite is true for those that are familiar with Filemaker but not with SQL?

[doltharz]

If I'm right, the magic stuff with $ and $$ to specify scope weren't yet "invented" in the mystic world of FM7 ?

[comment]

No, but global fields are available.