View with several columns linked to the same table

[BjornSkifs]

I have two tables designed as follows:

Table 1:

SalesDate Item1ID Item 2ID Item 3ID

Table 2

ItemID ItemName

People fill in Table 1 in a submission form. I know that it would be better to have them submit sales of each item separately, but for various reasons that's not an alternative.

Now, I have a problem when I want to display the results. I can display the results in a dropdown that uses Table 2 as a lookup table. The problem is that then it will be editable - and I don't want that.

If I only had one column for the items in Table 1, then I could use a view, but I'm not able to create a view that links both Item1ID and Item2ID with ItemID.

Am I doing something wrong? Is there a workaround?

I would be very grateful for any help.

/Bjorn

[MayMusic]

Bring an example of the workflow. What will be editable? Do you want to have a dropdown in a submission which has table 2 as lookup table? Do you have any sample page so I can see what you are referring to? :?: :?: :?:

[BjornSkifs]

First of all: Thanks MayMusic. You always help me out - and with excellent advice too. I really appreciate it.

Now, back to business.

I'm short on Datapages, so I'll give you a few screenshots.

Screenshot 1: This is the submission form where users report their sales.

It's a dropdown where the ItemName is shown but where the ItemID is recorded.

The submission writes to Table1 and Table 2 is a lookup table.

Screenshot 2: These are the tables, and some views.

In View 1, I have linked Table1_Item1ID with Table2_ItemID.

I would like to link also Table1_Item2ID and Table1_Item3ID with Table2_ItemID - as shown in the View wizard. But that doesn't work, as can be seen in View 2.

(The reason, I suppose, is that a View can only be a 2-dimensional matrix).

Screenshot 3: This is a details page drawn from Table 1.

I want to display the results in a datapage that is not editable.

Since I can't use a view, I might as well use Table 1 itself, which I have done in Screenshot 3.

Since I don't want it to be editable, I might as well use Display only, but then - as you can see - it only shows the ItemID, not the ItemName.

I could instead use a dropdown, using Table2 as a lookup table, but then it's editable - which I didn't want.

So in conclusion. I can't construct a view, since a view can only be 2-dimensional (or at least so it seems). And I can't use a lookup table, to display the name instead of the value, either.

(One workaround would be to record the ItemName and not the ItemID in Table 1, but the ItemNames changes quite frequently. That's why I used this approach to start with).

This was really long. I'm sorry about this. I hope it makes my problem clearer though.

Thank you for all your help!

Bjorn

[BjornSkifs]

One workaround, that unfortunately doesn't work very well, would be to create the tables Table 21 to Table 23 - all tables being identical to Table 2. Then I could create a view linking them in order to Item1ID, Item2ID, and Item3ID in Table 1, and effectively creating a 3-dimensional view.

The problem with this solution is that when a name changes I need to change it in all tables. (In my real world case I would have 12 tables). This is messy.

Is there a better solution?

Thanks!

Bjorn

[MayMusic]

Is your main concern is that the dropdown is editable and you do not want the user edit the data? If that is the reason then you can add a virtual field and show the value in that. This way they won't be able to update data. You can also hide the update button in the style by adding display:none; to .cbUpdateButton under Forms/Details -> Buttons in source tab.

[BjornSkifs]

Ah, sweet. Thanks!