Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

text calculation for multiple rows


sgoethner

This topic is 8191 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 8191 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.