I need Columns

[Papabjoe]

I have repeating information that I want to display using a portal. The problem is that my layout will not accomodate the number of records present. A scroll bar will not work for the type of report I am producing. What I need is two columns. I need the first field of Column 2 to succeed the last field of Column 1. Anyone have any idea's?

Papa.

[Logixx]

If you need 2 "columns" then you need two portals.

As you won't want both portals to show the same data, you'll need to break the ported file using a different relationship in each portal.

Create a calculation in the ported file where the result is 1 or 2 depending on the position of the record in the set (sorted the same way your portals do).

Involve that calc in the relationships.

Note: the scroll bar in portals is not an option. It springs as soon as the number of the ported records +1 exceeds the number of rows.

[Papabjoe]

I have no idea how to do that. Can you give me an example?

[McCormick]

Well, depending on how you're sorting your records, something will define which is first, second, etc. Date, or ID Number, or Age, or whatever.

Step 1) Create a calc field called COLUMN that does something like:

If (Date < 7/1/2003, "1", "2")

or

If (ID < 12345, "1", "2")

and have that first clause be whatever defines the cutoff point. This means you will need to pre-decide (and hard-code) that cutoff. You could also make it a variable with a little more work, but I don't know your needs well enough to try that.

Step 2) Create a calc field of whatever the old relationship field was (let's say RelateField), and concatenate (use the & symbol) with your new text field. You could also do both these steps in one more complicated calc field. So:

RELATE = RelateField & COLUMN

or

RELATE = RelateField & (If (ID < 12345, "1", "2"))

Step 3) Create calc fields in the other database that are:

RELATE1 = RelateField & "1"

RELATE2 = RelateField & "2"

Step 4) Create two relationships, not just one. RELATE to RELATE1 and RELATE to RELATE2. One portal for each relationship. Every record should show up in one or the other portal (which will look like columns) but not both. Then you just format it how you like.

This gets harder the more you ask of it. Adding a third colum is not so hard, but adding a _potential_ third column, that will only show up if you need it, is much more work.

I hope that helps. I'm pretty sure that's what Logixx was talking about.

[Logixx]

I'm pretty sure that's what Logixx was talking about.

That is.

I attach a demo.

Encode through the upper portal. You'll see that the other two portals will 'break' the ported records in two columns.

Everything is in the relationships.

I confess there are some glitches here, eg the deletion of portal rows will result in blanks in your columns. But that's just a matter of refining the calcs envolved in the relationships, which should be much more complicated therefore less educational. With this files, I just wanted to get you started with your columns.

Hope it helps.

[Logixx]

Here is the demo.

Demo.zip

[Papabjoe]

Great. I think I'm begining to understand.

I'm not having that great of luck. Attached is my effort.

myeffort.zip

[McCormick]

For the record (this is to help anyone searching this topic later), there is one important detail that can derail this. The ID numbers in the two records need to match for the relationship to work, and they will not match if one has leading zeroes and the other does not.

These two numbers do not match: "01234" and "1234".

This problem can happen when you are manipulating the IDs in calculation fields, but one original field was Text and one was Number. They can react differently to things like NumToText(ID).