Child info into parent

[mleiser]

I have parent records and related children records. If I want to put, say, all the first names of the children into the parent I can use the List function. I want to put each name into a separate field in the parent record. Is there any way to do that? Thanks. Michael

[comment]

16 minutes ago, mleiser said:

I want to put each name into a separate field in the parent record.

Why on earth would you want to do such thing? The very reason for having a child table is to avoid the need to have a field in the parent table for each child (which isn't really possible because you don't know in advance how many children a parent will have).

So what is the real problem you are trying to solve here? You can easily display the child names in separate boxes using a portal, if that's your goal.

 

[mleiser]

I knew someone was going to say that! Nothing to do with me. I have to send the names (along with other parent info) into an email program where we can create emails and send them out to every parent. I send the fields I need as well as the names of the children created by a list and they can't tell there's a carriage return between each child's data. I need each child's data on a separate line. They are definitely backwards. I don't need more than 4 names per parent so I was going to create 4 fields in the parent record to put into the email. I know it's going backwards but it's them that's the problem. Is there any way to do this? I use all the correct functions for everything else I do. Hope this makes some sense. Thanks. Michael

[LaRetta]

You can export instead from the child table and include parent information on the export.  This will give you one child per line with the parent info duplicating. 😀

If you wish one email to the parent instead, create calculation in parent: 

List ( childTable::childName ) 

Edited September 3 by LaRetta

[mleiser]

I use List all the time. The email application we use doesn't break up the list into separate lines. That is the problem. It's a problem with them. So I'm trying to get a workaround. Can't use separate records for each child. Need one record per parent with the children on separate lines so I figured separate fields for up to four children. That's what I'm trying to find out how to do. Michael

[comment]

5 hours ago, mleiser said:

I have to send the names (along with other parent info) into an email program where we can create emails and send them out to every parent.

Can you explain exactly what is the process of "sending" data to your email program? Are you attaching a file exported from the parent table? If that's the case, it's no wonder your email program cannot see the carriage returns in-between the items created by the List() function. It's because they are not there. When exporting to .csv or .tab format, Filemaker will replace them with vertical tab characters.

Now, there are several possible solutions for that (if that's indeed the problem we are dealing with here).  One is to export from the child table, as was already suggested by LaRetta. But that will probably not work for you, because it would mean duplicating the data from the parent record on every line (as LaRetta did mention). Another option is to change the vertical tab characters back to carriage returns in the exported file, before attaching it to the email. This can be done using the 'data file' script steps added in version 18.

You could also consider constructing the entire file in a global text field, then exporting that field's contents (you would probably want to post-process this file too to change its encoding from UTF-16 to UTF-8). Or you could export as XML and use a custom XSLT stylesheet to shape the resulting file to your liking. I mention these two options because they give you the ability to format your file in ways that are impossible with a plain export.

 

5 hours ago, mleiser said:

I don't need more than 4 names per parent so I was going to create 4 fields in the parent record to put into the email. I know it's going backwards but it's them that's the problem. Is there any way to do this?

Well yes, there is. You could simply construct 4 calculation fields using the GetNthRecord() function. But I would encourage you to attack the real problem rather than resort to such lame workaround.

Oh, and one more thing: this won't put the child names on separate lines. If indeed you are exporting the parent record, these fields will be placed on the same single line as all the other fields in the same record.

 

 

Edited September 3 by comment

[mleiser]

You are correct in your first paragraph. I create a csv file.

I cannot export by child table for the reason you said in the second paragraph.

Not being an expert, I don't understand your last method. However the data file script sounds good, but I'll have to find out what that is and learn it. Never heard or used it. Thanks so much for that idea.

Michaael

[comment]

Here is the relevant portion from a script I posted somewhere:

Set Variable [ $filePath ; Value:"Exported.csv" ]
Export Records [ “$filePath” ]

Open Data File [ “$filePath” ; Target: $myFile ]
Read from Data File [ File ID: $myFile ; Target: $csv_data ; Read as: UTF-8 ]

Set Variable [ $csv_data; Value:Substitute ( $csv_data ; Char (11) ; ¶ ) ]

Set Data File Position [ File ID: $myFile ; New position: 0 ]
Write to Data File [ File ID: $myFile ; Data source: $csv_data ; Write as: UTF-8 ; ] 
Close Data File [ File ID: $myFile ]

As I said, this will replace the vertical tab characters in the exported file with carriage returns. However, that does not mean that every child name will be on a separate line. The carriage returns will appear only between the child names, not before the first one or after the last.

 

[mleiser]

Being somewhat ignorant on some of the above, 

I have my parent database with the fields I need to export into a file called xyz.csv

One of the fields sent to xyz.csv is the field with all the names say it's called abc.

I want to convert abc in xyz.csv to have the CR between names.

Since I have xyz.csv does that mean the first two lines aren't needed?

Can you tell me where my file name and field name fit in in the above.

Again, sorry for my ignorance. I need to learn a lot more about filemaker and even move up from 18, but real busy being an administrator of a school with 700+ students besides doing the software for the school. One day I hope to learn more.

If you can help I'd appreciate it.

Thanks

Michael

[comment]

In my script, the exported file is named "Exported.csv" and this name (or more correctly, the path to this file) is stored in a variable and used both for exporting the file and for opening it as a data file.

You can use any file path you want for your export, but you need to use the same path for opening the exported file in order to fix it. That's why it's convenient to do both actions in the same script and use a variable for the file path.

 

[mleiser]

Thanks