2

I have two tables in Filemaker:

  • tableA (which includes fields idA (e.g. a123), date, price) and

  • tableB (which includes fields idB (e.g. b123), date, price).

How can I create a new table, tableC, with field id, populated with both idA and idB, (with the other fields being used for calculations on the combined data of both tables)?

Andy Hayden
  • 359,921
  • 101
  • 625
  • 535

3 Answers3

1

The only way is to script it (for repeating uses) or do it 'manually', if this is an ad-hoc process. Details depend on the situation, so please clarify.

Update: Sorry, I actually forgot about the question. I assume the ID fields do not overlap even across tables and you do not need to add the same record more than once, but update it instead. In such a case the simplest script would be like that:

Set Variable[ $self, Get( FileName ) ]
Import Records[ $self, Table A -> Table C, sync on ID, update and add new ]
Import Records[ $self, Table B -> Table C, sync on ID, update and add new ]

The Import Records step is managed via rather elaborate dialog, but the idea is that you import from the same file (you can just type file:<YourFileName> there), the format is FileMaker Pro, and then set the field mapping. Make sure to choose the Update matching records and Add remaining records options and select the ID fields as key files to sync by.

Mikhail Edoshin
  • 2,639
  • 16
  • 25
  • tableA and tableB are both updated in their respective layouts, and I'd like tableC to update itself, either at the click of a button, or automatically, so I think it ought to be scripted rather than "manual". Does that clarify the situation? Thanks! – Andy Hayden Mar 02 '12 at 09:16
  • What kind of script? I guess I want to somehow loop through all entries in idA and then all in idB... – Andy Hayden Mar 05 '12 at 11:54
  • it would be a filemaker script. feel free to contact me and i can provide you a sample db. – andyknas Mar 09 '12 at 02:55
  • Hi @andyknas, I realise it would be a filemaker script, but would be interested to see what the few lines of code would be. Do you think you could add it to your answer? – Andy Hayden Mar 12 '12 at 15:47
  • Since no one has given an actual script (which was the requirement in my bounty) I'm just going to award the bounty to the original answerer @Mikhail - who has answered several of my filemaker questions. – Andy Hayden Mar 13 '12 at 13:53
0

It would be a FileMaker script. It could be run as a script trigger, but then it's not going to be seamless to the user. Your best bet is to create the tables, then just run the script as needed (manually) to build Table C. If you have FileMaker Server, you could schedule this script to be run periodically to keep Table C up-to-date.

andyknas
  • 1,939
  • 2
  • 15
  • 29
  • I am somewhat familiar with filemaker scripts but in this instance I am at a loss of how to construct one which does what we want! – Andy Hayden Mar 12 '12 at 15:48
0

Maybe you can use the select into statement. I'm unsure if you wish to use calculated field from TableA and TableB or if your intension was to only calculate fields from the same table?

If tableA.IdA exists also in tableB.IdA, you could join the two tables and select into.

Else, you run the statement once for each table.

Select into statement

Select tableA.IdA, tableA.field1A, tableA.field2A, tableA.field1A * tableB.field2A
into New_Table from tableA

Edit: missed the part where you mentioned FileMaker. But maybe you could script this on the db and just drop the table.

amr-it
  • 21
  • 4