From 1-dimensional list to 2-dimensional representation?

[Electrumpeter]

Hi all,

I use Filemaker for quite a while but I am new to this forum. I am stuck with a problem and i don't know if it is solvable, but I think Filemaker should be able to deal with this.

I want to create a (graphical) table in which the items are all costs. Each row stands for a person and each column for a specification. I want to pick the data from a (filemaker)table that holds the following fields:

eventdescription

person

cost

description (of cost)

At each event one person will have more sorts of costs and I want to see all these costs on one line with the person.

When using a portal I get all the records for one person with the different sort of costs but then under each other. I can use summaries, but than I can't change the data anymore in this view and that is a prerequisite.

Is this possible in Filemaker (9)? Will it be possible in the future? Help is very welcome. Thanks in advance.

Hans.

[comment]

It is sort of possible: you can place a number of one-row portals on the layout, each starting at a different row. But the number of these "columns" will be fixed (and I wouldn't expect that to change any time soon).

[Electrumpeter]

Thanks for the quick reply!

I have seen that solution. You have to be certain though that there is a record for each description with each person even when the field can actually be empty and that the sort order is always the same in all these portals (difficult to check without the person being on the same layout) which is good in filemaker 10 which dynamically updates but not in 9 where I have seen the sort order being correct only after closing and reopening the file. But than still this feels as a way around something that should have some standard solution.

I feel this as a serious lack in the program.... Is it a known issue anyway?

Best, Hans.

[comment]

I'm afraid I don't quite follow what you're saying. There is only one portal, so it will be sorted the same for all records.

Perhaps you should elaborate on the nature of your data and your preferred view of it.

[Electrumpeter]

Sorry I had to do some other stuf these last days.

But in answer on your comment: Yes indeed. A portal will show the related records of another table. But it can only do so 1-dimensional.

Let’s say we have a table with records in which you have 4 fields.

Persons

Costs

costlabel (ie lodging, travel, sejours, and other costs)

Date

In another file I want to view these records per date in a 2-dimensional matrix:

Each row is another person and I have colums for the costlabels. I want to see what each person costed me on the specific labels lodging, travel, sejours.

I can do this by making three portals next to each other and sort them all in the same way, but then I would not have a horizontal view if somebody chooses not to travel on a certain day unless I introduce records with empty cost-fields. I also have to establish 3 different relationships based on the fields “costlabel” and “Persons” and I have to introduce 3 constants for lodging, travel and sejours.

It would be much easiër if I had a two dimensional portal in which I can show 1 field (costs). For each row I would sort them per person and for each column per costlabel. I can make use of a list to sort my fields in the preferred order horizontal and vertical.

I hope this is more clear.

Thanks, Hans.

[comment]

There are several ways to display data in this way* - but if you also want it to be editable, the options get very limited. Basically, you want a left outer join, which Filemaker doesn't do.

--

(*) See, for example:

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

[Electrumpeter]

you are right. This is what I want and I want it to be editable. I think I will resort to creating empty records for viewing purposes.

Why is it not part of Filemaker? Does not seem to difficult to program and I am not the only one who would benefit. Actually it is a good way to make filemaker more interesting for people now using spreadsheets I suppose.

Best, Hans.

Edited May 16, 2009 by Guest