sgoethner Posted April 29, 2002 Share Posted April 29, 2002 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 More sharing options...
The Bridge Posted April 29, 2002 Share Posted April 29, 2002 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 More sharing options...
sgoethner Posted April 30, 2002 Author Share Posted April 30, 2002 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 More sharing options...
The Bridge Posted April 30, 2002 Share Posted April 30, 2002 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 More sharing options...
sgoethner Posted May 2, 2002 Author Share Posted May 2, 2002 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now