Extract data-MULTIPLE records-appear in ONE field

[HonkyCat]

Hello,

I posted this question in the "Script" section and hadn't gotten an answer just yet. I thought I'd post the question here since I'm thinking this is going to have to take place in a calculation field.

I have created a calculation field which extracts data from a record to use in a "send mail" script. The problem I'm trying to solve is how to extract our report number (number field called "Rep No") from EACH record I'm browsing and send it in ONE email. The records I'll be browsing are all for the same customer, but I need each individual report number to show up in ONE email. I don't want to have to send multiple emails to the same customer. As it stands, I'll have to have the script stop in order for someone to manually enter the range of report numbers in the email and then send.

In addition, sometimes the report numbers are in order and sometimes they are not. So for example, I could not always put 04-00001...04-00015. They could be 04-00001, 04-00005, 04-00016, and so on.

Thanks for any help you can give me!

Kristine

FileMaker Version: 6

Platform: Mac OS X Panther

[andygaunt]

Hi -

You may want to look at the copy all records command.

By using a Go To Layout command and then using Copy All Records it will copy whatever is on that layout and place it on the clipboard in a return delimited format. You can then paste this into another field (say a global to hold the values, gHoldRepNumbers, result of text)

So, if you create a layout with just your report number field on it and add to a script

Go To Layout [Rep # only]

Copy All Records

Go To Layout [ a layout with the field to paste into on]

Paste [selected field]

You could include a sort command before the copy all records to sort the data into order. Do a manual sort and then add the sort command with restore sort order and perform without dialog checked.

Then you can use this data in your email.

Does This Help?

[HonkyCat]

Hi Andy,

Yes, thank you, that does help a lot. I have a question though, I realized that I actually need more data from all the records than just the report number. I know how to do that now, but is there a way for me to display the data so it shows up nicely? For example:

04-00001 (paragraph)

Other Info. 1 (paragraph)

Other Info. 2 (paragraph)

etc., etc.

Thanks!

Kristine

[andygaunt]

Kristine -

What exactly is the "other" info and the paragraph.

You may want to create a calculated field containing your data and then placing that on the layout for the copy all records command.

Might help.

[HonkyCat]

Hi Andy,

The other information I need to show up besides the report number is the status of their test (whether it passes or fails), the specification, information about what they are testing, etc..

I tried what you said, it indents between records, but it runs everything together when I say "copy all records". I would like it to appear with returns between Report No.: xx-xxxxx, Status: This sample COMPLIES with, etc.. Or even just spaces would work. This is what it looks like (even though it's formatted exactly the way I want it in the calculation field)...

Report No.: 04-xxxxxStatus: This sample COMPLIES with the above referenced specification.Spec: Results vs NFPA 1500-2002 & CGA G-7.1-1997 Grade E Gas Quality Specification.Air Source I.D.: Compressor, Model xxxxxxx, S/N xxxxxxxxSampled for (if applicable): xyz company

Report No.: 04-xxxxxStatus: This sample COMPLIES with the above referenced specification.Spec: Results vs NFPA 1500-2002 & CGA G-7.1-1997 Grade E Gas Quality Specification.Air Source I.D.: Compressor, Model xxxxxxx, S/N xxxxxxxxSampled for (if applicable): xyz company

Any ideas?? Thanks!

Kristine

[Ender]

Maybe you can use a calculation to build the fields in the format you want and a script to loop through the records and concatonate the data into one global. For multiple customers, you would have to mark the records that have already been sent and then make sure to omit those when you're pulling up the set of customers to email.

[Ender]

Even better - just make the looping script build the fields in the format you want:

Set Field [ gEmailBody, gEmailBody & "<Paragraph> Report #:" & Report Number & " Status:" & Status ...]

[HonkyCat]

Hi Ender,

On the first suggestion, I tried that, but it does not put any sort of paragraph between the two RECORDS. The first record looks good, but the first line of the second record begins on the same line as the last line of the first record. Hope that made sense.

On the second suggestion, I did the "Set Field" and had it build the field the way I wanted. It looks great (I even learned something about "set field" I didn't know before...thanks!), but when I loop, the "Set Field" wipes out the data from the first record and replaces it with the next records data. I don't have the option of concatenating the data. Any other ideas? Thanks for your help!! I'm getting so frustrated with this!!

[Ender]

Your almost there. Look again at the Set Field:

Set Field [ gEmailBody, gEmailBody & "<Paragraph> Report #:" & Report Number & " Status:" & Status ...]

The first thing in the Set Field calc is the field that you are setting. This puts the previous field contents in there first. Then when I say <Paragraph>, I mean the funny P symbol that means a paragraph return--you'll see it in the calculation dialog by the +, -, and other symbols. The paragraph symbol should be inserted in the quoted text.

[HonkyCat]

Thank you Ender! I could give you a BIG HUG right now!!! It works great!! I got the part about the paragraph, but I was off on what you were originally telling me. Anyway...it FINALLY sunk in!! Thanks so much...I was about to start pulling my hair out!!

Kristine