text calculation for multiple rows

[sgoethner]

Hello,

My trouble spot today:

My portal contains street, city, and phone. In the main db I need to build a calc field that brings all the rows together into one text field.

row 1 - street, city phone

row 2 - street, city phone

. . .

Is there a way to do this? (like GetPortalRowNumber?)

Thanks,

Steve

[The Bridge]

One possibility:

In your related DB, define a calculation field (stored, text)

Concatenated_Text_Field = Street & ", " & City & " " & Phone

Then in your main DB, the one with the portal, define a value list based on the same relationship as the portal, pulling values from Concatenated_Text_Field in the related DB.

Then in your main DB define a calculation field like so:

Concatenated_Related_Fields = ValueListItems(Status(CurrentFileName), "ValueListName")

I've used this technique a couple of times. However, be aware that this technique is not always reliable, especially if I'm viewing records in List View or Preview Mode. I find it most useful not in calculations but in scripts. In fact, you might prefer to populate a standard text field using the above calculation within a script.

[sgoethner]

Hello,

I was unsuccessful in getting the last calc to work. So, some more detail to see if there are other insights. The goal is to produce phone directories. I have:

a client file

an address file; one client can have many addresses, especially medical groups

a listing file; this is where I'm trying to bring things together

and a join file for the listing db and the address db

A client can be listed in more than one directory and each listing can be different or the same. In the listing db I pull some header info from the client db. I flag the addresses that need to be used (a client could have 100 address/phone #s and use 15 of those in 1 listing) via an address code and a portal in the listing db showing the join file. The listing db also has separate groups of repeating fields for each of 12 directories. The repeating fields manage what and how many headings this listing is utilized for.

example of listing:

Medical Group

Come to us for great health care

124 Main St 555-1212

Fax 555-1234

24 hours 555-8765

This listing could appear under:

Medical Groups

Physicians & Surgeons

Podiatrists

So, I need to build the listing from the addresses line by line in a single text field and export them for however many headings are needed to Quark XPress for production. I am trying Not to use lookups in repeating fields for the addresses because there are many changes made to the data along the way. By keeping the info relational I keep the data errors down by restricting changes to the address db. Also, I'm trying to avoid using a script to copy and paste because that is temporary (based on the timing of when it was last run) and it affects thousands of records. I'm hoping for (seems like wishful thinking) an automatic solution, as I build the listing by identifying the addresses to use that my output text will be prepared.

All ideas are greatly appreciated!!

Steve

[The Bridge]

Sorry the calc didn't work out for you. No matter; what you want to accomplish can be done without it, and (I think) pretty easily.

I imagine your system can be set up like this (I'm working from the ground up here, so bear with me)

Fields:

Client DB: (1 record per client)

Client_Key_p (auto-entered unique parent key)

Company Name

etc.

Address DB: (1 record per address, 1 or more addresses per client)

Address_Key_p (auto-entered unique parent key)

Client_Key_f

Street Address

Global_Access = 1

etc.

Listing DB: (1 record per listing, 1 or more listings per address)

Listing_Key_p (auto-entered unique parent key)

Address_Key_f

Client_Key_f (Look-up based on relationship Address_Key_f to Address_Key_p)

Heading

Global_Access = 1

Note: this DB stores the Heading for each listing and just references to clients and listings

Relationships:

Client DB:

Client_Key_p to Address:: Client_Key_f (all of a given client's addresses)

Client_Key_p to Listing:: Client_Key_f (all of a given client's listings)

Address DB:

Address_Key_p to Listing:: Address_Key_f (all of a given address' listings, allow creation of related records)

Global_Access to Listing:: Global_Access (used to generate Heading Valuelist)

Listing DB:

Client_Key_f to Client:: Client_Key_p (a given listing belongs to this client)

Address_Key_f to Address:: Address_Key_p (a given listing displays this address)

ValueLists:

Address DB:

vl_Headings (from field Listing|Global_Access:: Headings)

Next:

Get rid of those repeating fields.

In Address DB, create a portal showing related Listings. Each portal row contains the Listing DB:: Heading field, which is formatted as a drop-down list (or menu, or whatever) from vl_Headings.

When it comes time to export or print your listings, do it from Listing DB. Sort by Heading and Client Name. You can display the Company Name and Address (again, one per record) using related fields from their respective DBs. Use Summary Parts to avoid repeating the Headings field for every listing.

I hope this is clear. Let me know how it works out.

[sgoethner]

Hello Lungfish,

Thank you for your ideas. In the interim I changed my position on using a script to copy and paste. It's quick and easy. I need my client to make sure they run the script at the appropriate time. FYI - I believe I need the repeating fields because the heading list can change in each directory. When I export to XPress I will have the listing group that's been copied & pasted go with each split heading.

Thanks again for your input!

Steve