Multiplying Text Fields....?

[Kurt Knippel]

I have 2 multi-line text fields and I need to combine all the lines in one field with all the lines in another field.

For example:

Field1 =

John

Mary

Jim

Field 2 =

4/14/02

4/16/02

4/17/02

The result that I need is:

John - 4/14/02

John - 4/16/02

John - 4/17/02

Mary - 4/14/02

Mary - 4/16/02

Mary - 4/17/02

Jim - 4/14/02

Jim - 4/16/02

Jim - 4/17/02

I have the various plugins such as Troi text, plus a whole host of others.

I can accomplish this via a loop, but I was looking for a calculated way to do this.

Any ideas?

[Fitch]

Would a script be ok, or does it have to be done via calculated fields?

[Chuck]

I'm almost positive I could come up with a calculation that could do it, but there would be a limit on the number of lines it could handle. Is there a limit, or could it be hundreds?

Chuck

[Kurt Knippel]

quote:

---

Originally posted by Chuck:

I'm almost positive I could come up with a calculation that could do it, but there would be a limit on the number of lines it could handle. Is there a limit, or could it be hundreds?

---

The Name field will contain a maximum of 5 names. The Date field could concievably contain dozens of dates, but will realistically only contain say a max of 10 dates.

[Chuck]

Kurt,

Here's what I've come up with. It will handle up to three lines for each field and will handle the possiblity of there being a different number of lines in each field. I'll let you extrapolate the calcs to handle up to five names and 10 dates, or however many you need. These don't take into account that the total possible of lines for the two fields is different, but I'm hoping that after taking a look at this you'll see the thinking.

These calcs would be easier with the Troi Text plugin which can extract lines of text more easily than all this Left, Middle, Right and PatternCounting can. But this will work with only FileMaker.

I've broken it up into multiple fields to do the job, but it could all be combined into one field. Using multiple fields makes it easier to follow what's going on. Every field is a calculation field with a text result.

code:

---

Field11 = Left( Field1, Position( Field1, "

[Chuck]

Kurt,

BTW, there is a bug in those. If there are more names than dates, it doesn't work because I only check for the number of lines in the names field. But again, take a look at them and see if they don't at least get you going.

Chuck

[Fitch]

The reason I asked about calc vs. script is that with a calc, you'd have to limit yourself to a certain number of names and dates. Chuck knows what I'm talking about. But with a script, you can count your way through the 's with a couple of globals and a loop. I demonstrated something similar in a recent topic on find requests. Sorry I don't have time to work out the whole solution just now.

[Kurt Knippel]

Actually thanks for all of your help. This got me unstuck and I solved the problem with a combination of script and calcs.

[Kurt Knippel]

quote:

---

Originally posted by Fitch:

Would a script be ok, or does it have to be done via calculated fields?

---

I am hoping for a calcualtion, but a script is fine.

[djgogi]

quote:

---

Originally posted by CaptKurt:

Actually thanks for all of your help. This got me unstuck and I solved the problem with a combination of script and calcs.

---

Well, if you don't mind having duplicates values in the field (it's an compound key, isn't it) than you could use something like this

code:

---

Substitute(Substitute(text1 & "