Combining datas from 2 different Tables

[Lawrencex]

Greetings All FileMaker Friends;

 

I would appreciate help for the following:

 

How do I combine datas from 2 or more Tables, in different files into

a my current Table, without using "Import" option.

 

Thanks in advance.

 

Lawrencex

[Wim Decorte]

Define "combine": 

 

- do you just want to see data from two tables on one layout?

- or does the data permanently need to be moved from one table to the other and live there?

 

Can you expand a bit with a real world example?  Otherwise it is a little too abstract.

[gczychi]

Hi,

 

might I hop onto this topic?  I have exactly the same question and I think everyone could benefit.

 

In my case, I have two tables (Beleg, Buchung) and I need to show some records of the first table along with some records of the other table. In each table I will pick up a different set of fields so that the combined data makes sense.

 

In the resulting table (Export), I would define a key who's value occurs in records of the first table, let's say BelegTyp. I could do the same for the other table, say BuchungTyp. I would like to display in table 3 (Export) all records from table 1 who's Beleg::Typ field < 2  and at the same time all records of table 2 (Buchung) whose Buchung::Typ equals 1.

 

Can this be done elegantly?

 

Thanks a lot!

 

Gary

[comment]

Please explain your purpose in this. It is very difficult to follow without knowing what these tables represent and what is the relationship (if any) between them.

 

 

In each table I will pick up a different set of fields so that the combined data makes sense.

 

 

But in the combining table there can be only one set of fields - so how exactly will your layout look?

[gczychi]

These two tables have different data, one (Beleg) contains receipts and the other (Buchung) accounting entries. There is a one to many relationship for each record in Beleg  to Buchung with different values in Buchung::Konto. Buchung::Betrag and some other fields. Then, in Buchung Layout there are some related fields showing data from Beleg that haven't changed in Buchung. All related fields are shown in gray in the screen shots (most gray fields in Buchung layout are related from Beleg).

 

For another company to process the data further, I need to export both tables, but they can read only one table. Each record of the combined table will have fields like this:

 

If the record originates from Beleg, it has Beleg::Konto, Beleg::Valuta, Beleg::Buchungstext, Beleg::Betrag

If the record originates from Buchung, it has Buchung::Konto, Beleg::Valuta, Beleg::Buchungstext, Buchung::Betrag

The table Export need to look like:  Export::Konto, Export::Valuta, Export::Buchungstext, Export::Betrag

 

I hope I could make myself clear.

 

Thanks!

 

Gary

 

Beleg: 

Buchung: 

[comment]

For another company to process the data further, I need to export both tables, but they can read only one table.

 

If this is only for export, there might be other ways to handle it. Please add more details about the required export format - e.g. the file format, the order of the records (if any), etc.  Will you be exporting all records from both tables, or only found sets - if the latter, how will these be established. And most importantly: will you be exporting any widows or orphans.

[gczychi]

Semicolon-delimited CSV in Windows ANSI format. Because FileMaker will only export komma-delimited files, I am exporting in the Merge file format, renaming from .mer to .csv and then it works. I have successfully exported the Buchung table and they could read it. You can see the export fields that I have defined in the screen shot below.

 

There is no particular record order necessary but would be good for human (my) readability to look at (and check) the data before exporting. From both tables I need FoundSets, not all.

 

From your words, I assume that there is no elegant way that FileMaker could accomplish this, to merge (or join) two tables in a specific way -- using relations. That's disappointing. I was a bit reluctant to use brute force (meaning looping through these tables and copying, calculating, creating the records and setting the fields in the Export table). But if that's the way, then I will do it. I hate these scripts; they are long, time consuming and error prone.

 

Or, do you have any other suggestions?

 

Thanks a lot,

 

Gary

 

 

[comment]

From your words, I assume that there is no elegant way that FileMaker could accomplish this, to merge (or join) two tables in a specific way -- using relations. 

 

I would not jump to conclusions. At this point, I cannot say one way or another, since you have not answered my questions.

 

 

Will you be exporting all records from both tables, or only found sets - if the latter, how will these be established. And most importantly: will you be exporting any widows or orphans.

[gczychi]

Sorry, I didn't know what you meant by widows and orphans in this context. Do you mean single records that need not to be exported? If so, yes. Only a part of the records of each table will need to be exported. In other words there are specific FoundSets of both tables that need to be exported.

 

At the moment I am working with FoundSets (Find records) to show what I need and then export it, manually. Of course, I would prefer using foreign keys. For example, there is a Beleg::Typ field that contains values 0, 1, 2 and I will need to export all records whose Beleg::Typ field equals 0 or 1. I have something similar in the Buchung table.

 

Is that what you mean?

[comment]

By "orphans" I meant: will you be exporting any Buchung records without including their parent Beleg records in the exported set?

By "widows" I meant: will you be exporting any Beleg records without including any of their Buchung children in the exported set?

 

If there is some logic to the exported set as a whole (for example: find child records where Buchung::Field_x = ? and whose parent Beleg::Type = ?), then an elegant solution may exist. Otherwise you will have to compile two unrelated (literally) found sets.

[gczychi]

Ah, ok. To clarify. All Buchung records have a Beleg parent. Beleg records have 0 to 5 Buchung records as childs.

 

Orphans: No: For each Buchung record, their parent must be there (only once).

 

Widows: Yes, see below:

Beleg::Typ 0: records don't have childs but they must be present

Beleg::Typ 1: records all have Buchung childs (1-5) and they must be present

Beleg::Typ 2: records have Buchung childs but only the Buchung record of the first beleg record must be present.

However, I can see that this poses a problem, therefore I could divide the current Beleg::Typ 2 in two types: 2 and 3:

Beleg::Typ 2 (new): records have one Buchung child which must be present

Beleg::Typ 3: (new) records have one Buchung child which must not be present

 

I hope it makes more sense now.

[comment]

If you're exporting widows, then you cannot use relationships in this. Here are a few options you could explore.

 

1. Establish the two required found sets in the two tables. Import both found sets into a third Export table. To make this simple, define two calculation fields in the Buchung table:

 

cValuta = Beleg::Valuta,

cBuchungstext = Beleg::Buchungstext

 

and import those when importing from the Buchung table.

 

 

2. Establish the two found sets as before. Export the records in two steps (scripted, of course): first, export one of the tables as XML to the temp folder. Next, export the other table as XML, using a custom XSLT stylesheet to read the previous export and combine it with the export in progress to produce the CSV file. This has the advantage of practically zero footprint in your file - however, it does require at least an intermediate knowledge of XSLT.

 

 

3. Use two Execute SQL() functions to create the CSV as text in a global field, then export the field contents. Note that exporting field contents creates a UTF-16 encoded file, which not all applications know how to handle. However, there are workarounds for that, too.

[gczychi]

Wow, these are really cool tricks! I didn't know that you could do 1) and 2), but thought about SQL. I could theoretically import UTF-16 into Excel but I have my doubts, as Excel doesn't even know how to read the normal FileMaker UTF-8 (though it says it can).

 

What if I get rid of the widows (I could clean them up later or maybe find another way to deal with them in the Beleg table)?  Would that be easier in terms of a relation? How would I do it?

 

You are very knowledgable and a very good help. Thank you!

[comment]

What if I get rid of the widows (I could clean them up later or maybe find another way to deal with them in the Beleg table)?  Would that be easier in terms of a relation? How would I do it?

 

I should say something before answering this: if you were asking how to export records from one table, resulting in a semicolon-delimited CSV file, I would say export as XML and use XSLT to produce the text file. The XML/XSLT option is a very powerful feature - sadly, also one of the most under-utilized ones.

Now, if you were exporting only related records, then option #2 could be performed in a single step. It's actually even more powerful than that, since you can use the XSLT stylesheet to exclude some of the exported records from the result, using predefined criteria. For example, I could export all invoices for the month of May 2014, together with all their related line items, and tell the XSLT stylesheet to exclude invoices under $1,000 and line items of type service - resulting in having widows and/or orphans in the final output.

 

 

 

I could theoretically import UTF-16 into Excel

 

I am not sure how Excel figures in this. Speaking in general, Excel is probably the worst choice as an intermediate format.

[gczychi]

Thanks, again. I probably go for the first solution, as I don't have the time to dive into XSLT.

 

 

 

I am not sure how Excel figures in this. Speaking in general, Excel is probably the worst choice as an intermediate format.

 

You're probably right, but it's the best choice for making CSV files.  FileMaker is a nightmare, as you need to export in Merge format, then rename .mer to .csv and on top there are issues with the character set.

 

 

Regards,

 

Gary

[comment]

you need to export in Merge format, then rename .mer to .csv

 

This part at least is not necessary: you can name your exported file whatever you want, including the extension.