One field to hold multiple records

[Robert Collins]

I am looking to find a way round a problem I have.

I want to have a script (scheduled by FM9 Server) to email a query list to a supplier but because Server 9 doesn't support 'save as PDF (Perform without dialog)' sending as an attachment is not practical. So I would like to list the records within the body of the email.

My script finds the records in question but I need to list some information from those records in my email. I thought about creating a globalfield and looping a SetField , pasting the info into the global field but it sounds a bit clunky.

So I am wondering if there is a more elagant solution available

thanks

[Raybaudi]

Hi

maybe this can give you some help:

http://fmforums.com/forum/showpost.php?post/303534/

[Robert Collins]

thanks, that almost solves the problem as I only need info from found set and not all records.

Any ideas

[comment]

There are many methods to collect data from the found set, such as Copy All Records (requires a dedicated layout with only the desired fields), a recursive custom function, a looping script (like the one you have), using Replace Field Contents on a global field and more.

For a limited number of records, looping through the records while setting a global (not pasting!) is perfectly acceptable, esp. when you're within the context of a script anyway.

See also:

http://fmforums.com/forum/showtopic.php?tid/197149/

[Robert Collins]

Ok, I have field that contains the contents of the four fields I need. I am going to setup a loop using Set Variable [$complete_query_line ; Value:Purchase Line Items::Complete_query_line]

if I then use Replace Field contents or use Set Field to insert the variable into it , won't it replace the previous data (Surely I want it to add to the field.)

[comment]

Looping:

...

Set Variable [ $result ; $result & Table::Field & ¶ ]

Go to Record [ Next ; Exit after last ]

...

Replace:

Set Field [ Table::gResult; "" ]

Replace Field Contents [ No Dialog ; Table::gResult ; Table::gResult & Table::Field & ¶ ]

---

BTW, the intermediate field is not necessary for this, since you can use the same calculation directly within the script step.

Edited September 20, 2008 by Guest

[Robert Collins]

thank you very much , you have solved my problem

[Robert Collins]

right, one quick question!

I have the field that contains all the fields of one record.As the data in the various fields is not exactly the same for each record, is there any way to format the list to tidy up so that everything lines up in my global field?

here's my current calc field for holding 1 records worth of data:

Complete_Data_Line

Substitute( List (total quantity ordered ;"£" & cost price ; "£" & total line cost ; Product file::Combined_make_model_type ) ; ¶ ; " " )

thanks again

[comment]

You can use a tab character instead of space. That will work fine in Filemaker, though it may not in your mail program (or the recipient's). Alternatively, you could pad each element with trailing spaces (with the amount of spaces calculated according to the length of the element), but that would work only with fixed-width font.

Note that you cannot type a tab in the calculation formula; you need to copy it in Browse mode and paste it into the formula.

[Cabinetman]

Beat me to it......... This helped me a bit to!

My exception is I have only 1 field in the list and I need to replace the ¶ with a comma - which I can do - and then break it up every 10 records.

Reason: I can only send 10 requests grouped together at a time.... total original records anywhere from 100 - 100,000.

So I get:

record1,record2,record3,record4,record5....record10

record11,record12,record13,record14........record20

etc...

Then I have to get each segment of 10 into I'm guessing new records to script out the export.

Even better is there are some duplicates that I'd Like to remove from the list... but that may be to much for my simple mind........

PS Currently at.... Substitute ( List ( table::field ) ; ¶ ; "," )

Edited September 23, 2008 by Guest

[comment]

Filemaker has no function to substitute every n-th occurrence of a string. You would need to use a custom recursive function for this, or a looping script. If the final result is records, I would go with a script.

[Robert Collins]

thanks, it seems tabs are not suitable as at least two of my fields vary so much in length , its throwing everything else out of alignment.

I will have a serch for a custom function to count spaces (or something to that effect!)

[Cabinetman]

Seems like a simple (yeah right) recursive function that takes every - in this case - 110th character and replaces it with ¶ and sets a new text field might be easier and quicker... not sure.

Doesn't seem all the unusual to me but who am I?

[comment]

Tab placement can be adjusted (in a Filemaker field, at least). In Layout mode, select the field, and choose Format > Text… > Paragraph… > Tabs.

You don't need a custom function for the spaces - something like:

text & Left ( Substitute ( 10^n - 1 ; "9" ; " " ) ; n - Length ( text ) )

will append the correct amount of spaces to text to create a string of n characters in total.

[comment]

recursive function that takes every - in this case - 110th character and replaces it with ¶ and sets a new text field might be easier and quicker

It would be every 110th character only if all the records are of the same length. Otherwise it needs to be every 10th occurrence of ¶.

Doing it a calculation field might seem easier, but a calculation cannot create new records. And if a script is required anyway, then why not put the entire logic in a single location?

[Lee Smith]

This old tread might be of interest.

Link

[Raybaudi]

Note that you cannot type a tab in the calculation formula; you need to copy it in Browse mode and paste it into the formula.

Hi comment,

I don't know on Mac, but on Win it's possible...

simply press both Ctrl + Tab

[Cabinetman]

Back from lunch!

First........

This old tread might be of interest.

Link

Nothing like a re-tread...lol

Second........

while eating and considering I think you're right on the script. I'm gonna try using some fields to test.

Field1 = 1234567890,0123456789,9012345678....

Field2 = set with the first 109 characters.

Field3 = set with the rest.

Basic Script idea:

Take field1, set field2 & field3

export/import based on field2

loop

set field1 with field3

Take field1, set field2 & field3

exit when field3 is empty

end loop

Not sure how it'll work when there are less than 109 characters left but we'll see.....

I don't really have time for this but I need it working

[Cabinetman]

Not sure how to get everything on the right of 110 characters to set into field3

set field1 with my list is ok I get all with comma's

set field2: Left ( table::field1 ; 109 ) is ok

set field3: ......ugh

Don't laugh (please)I'm trying...using:

Let (

$characters = GetAsNumber ( ASINS::ASINS Text ) - 110;

Replace ( ASINS::ASINS Text ; 111 ; $characters ; Right (ASINS::ASINS Text ; $characters ) )

)

Help!!!

[comment]

I am not sure where does this lead, but the "other" part of:

Left ( table::field1 ; 109 )

is:

Right ( table::field1 ; Length ( table::field1 ) - 109 )

[Cabinetman]

So I thought to but when I put it in a calc field I get the whole contents of field 1.......

Ooops I was looking at the wrong field!

Holy Cow !!

PS: had to change it to 110 to get the last comma out.....

Thanks!

Edited September 23, 2008 by Guest

[Robert Collins]

Thanks for the reply , I am not sure how to incorpoate your answer into my db so I have attached an example.

I basically want the field Combined_line_information to look neat and tidy.

Also total_cost field doesn't show to two decimal places (like formatting a number field does)

thanks for all your help

[Robert Collins]

sorry,

[comment]

Forum's attachments do not work at the moment. You can see a simple example of tab-formatting a field here:

http://fmforums.com/forum/showtopic.php?tid/195005/post/289224/#289224

Re formatting a number in a calculation see:

http://fmforums.com/forum/showtopic.php?tid/188158/

[Robert Collins]

thanks, just re-formatted the number and that works fine.

regarding the tabs/spaces issue - I looked the thread you provided a link to but I don't think that is the answer.

from my (limited) understanding, inserting a tab between fields such as:

Field 1 & tab & field 2 & tab

is fine if I want the field to look right on a layout. the problem comes when I use my List(complete_data_line) calc with my email plugin as its going to ignore any fomatting I make to any layout I have.

[comment]

That's what I meant when I said earlier:

You can use a tab character instead of space. That will work fine in Filemaker, though it may not in your mail program (or the recipient's).

I am afraid I don't know of a good solution for formatting tabular data in the body of an e-mail message. This has nothing to do with Filemaker - how would you do it if you were composing the message manually?