Cartesian Product Portal?

[cchervit]

all,

been reading posts here the past week (since i've started with FM7) and i've found it truly valuable. thank you! been able to accomplish most everything so far, except for this challenging requirement. thought one of you may know of a solution.

i've got 3 tables -- location, group, and permissions. the location and group tables are simply lists of possible entries, and are not "user-editable". the permissions table has a primary key of loc_id + group_id, and has a 3rd column of a permission value. it will contain a cartesian product of all location+group combos, plus the corresponding permissions value for each combo.

i'm trying to create a form where a user sees a header of a group (uneditable), sees a row for each location (uneditable), and next to each location row, the user has an entry-field for the appropriate permission for the given group/location combo. btw, i've already created dummyvalue=1 in each of the location and group tables, that enabled me to get a layout which prints the group as a header row, and all the locations underneath, and repeats for each group in the groups table.

however, i can't get anything to work where i have a user-entry field pointing to the permission table so that the user can enter the appropriate permissions value for the given group/location s/he happens to be on.

can anyone figure out how i can design this layout? i've tried many things, but nothing seems to work so far.

thanks in advance,

chad

[comment]

I am not quite sure I understand this - see if the attached helps.

CartesianGrid.fp7.zip

[SlimJim]

Another possibility is to create a layout in the group table with a portal into the permissions table (related via the group key only) and put into the portal the two fields: location, permissions. Thus for a given group record you can see all the available locations in the portal (possibly with scrolling) and be able to alter your initial permissions numbers.

[cchervit]

hey, comment. that's exactly it (or close enough).

now, perhaps you can explain a couple of things that are not too obvious to me? i'm used to designing straight sql databases, and it seems fm7 does some unique stuff.

1) what's the purpose of gCustomerID? is it just a virtual field? does it take up storage space?

2) what do the gDeleteButton and cDeleteButton do? i can't seem to find any "meat" to them except for a case(value) command.

thanks for your help.

chad

[cchervit]

Another possibility is to create a layout in the group table with a portal into the permissions table (related via the group key only) and put into the portal the two fields: location, permissions. Thus for a given group record you can see all the available locations in the portal (possibly with scrolling) and be able to alter your initial permissions numbers.

slimjim, i was thinking along the same lines, and i tried to do that first, but it didn't seem to work. perhaps i've gotten a relationship wrong somewhere? if you have a spare moment, and can look at the last layout, i'd love another set of eyes on it.

thanks,

chad

Permissions.fp7.zip

[comment]

1. gColumnID (not gCustomerID) is a global field. It holds one value (per user) that is used for all records in the file. Its purpose here is to identify the currently selected column (the current row is the one the user is in).

Values is a join table between Rows and Columns. In order to automatically enter both RowID AND ColumnID into a new record in Values, one of them needs to be supplied from an artificial source, since you cannot be in both parent tables at the same time.

2. gDeleteButton is a global container field. It holds the button grafic as a source for cDeleteButton. cDeleteButton displays the grafic, if the record holds a value.

Had I placed a button directly on the layout, it would be displayed for all Row records - irregardless of whether a related Value exists or not. Instead, the graphic is displayed in a portal.

The calculation is still needed, because the relationship allows creation of records, therefore there is a constant "ghost" record in the last (and only) row of the portal.

Note that records in Values are created as needed - there are no empty slots.

[SlimJim]

It took me a while to relate your sample to the description. I hope I have got it right. The problem is that in the final layout you are trying to fill out a cartesian product which has not yet been formed. What I have done is to put a button on the layout (you can always remove this if it is a problem and go to the script menu) which fills in the foldernames in the portal- creating the records as it goes. Once that is done the permissions can be filled in. The foldername field is still inaccessible in browse mode so users cannot change the contents.

When you want to do this for another group you simply move to the next group record. (e.g.

use a navigation button)

Permissions.zip

[cchervit]

slimjim, thanks for the file. i never thought about having a fill button.

comment, i think your solution definitely fits what i want to do, especially b/c there's no records in the permission table until something gets filled in. thanks, also, for the explanations. fm7 is such a different beast from what i'm used to.

where did you get that delete button and how do i manage to copy it into my database? copy/paste doesn't seem to work (well, i got the image, but the button still doesn't work the same as yours).

i really appreciate both of your suggestions and time given. you've both helped me out.

chad

[comment]

The button is a graphic. It lives in the gDeleteButton field (type container).

The button definition is attached to the cDeleteButton field on the Create layout.