Cannot do the mapping during import?

Questions
1

Hi, I have posted a question in community, but I would like to see if anyone could help me here.

I could not do the mapping between 2 tables. There are 33 fields in table A and 37 fields in table B so I would like to update the record in A using table B(source file) if there are no matching records, a new one will be created in table A. The problem is some fields cannot be matched.

Also, there are another problems, sometimes the default source table is changed to table C so I cannot do the mapping. I have to do it manually.

Does anyone could help me? I have tried FM17 and 18 without success.

If it is not ok to post here. I will delete this post.

2

Welcome to the forum!

What do you mean you can’t do the “mapping”?

Do you have a primary key/foreign key relationship set up?

How are you trying to do the update? A script?

Please post an image of the relevant part of your relationship graph and also your script.

You could still do this UPDATE using SQL, but not within FileMaker itself - but still using FileMaker and it’s JDBC driver.

1 Like
3

Hi @samfmp,

@anon45965781 asked good questions. Providing a sample file with some sample records in table A and B would help a lot figuring out how this can be fixed.

1 Like
4

Are some of the fields that can’t be mapped calculation fields by any chance?

1 Like
5

Thank you. The situation is like this sample fileFieldMapping.fmp12 (216 KB)

26
so the expected mapping is like this.

53
When I press the button again, it will change the source to table 1.

10
When I changed back to table 2, the mapping is broken.

so I could not do it automatically.

6

I haven’t looked at your demo file, but it looks as if you are trying to import from and to the same table occurrence within the same file, or are you importing from a separate file?

You can import from the same table into the same table, but you’d need to do it from a separate table occurrence within your relationship graph.

Again, above having only taken a cursory glance at the screen images.

7

Ah, should have looked at your file first. You can’t import into calculation fields

8

No, just importing record from Test2 to Test1. And there is a calculation for other purposes, and I would like to import from e to c.

However, I could not save the mapping in the hosted file.

9

We always script any form of import. Even if we’re doing data work in a development environment, it doesn’t matter how many times we think we’re only going to import a specific mapping, we always seem to need to do it again at some point.

Hence, I’d recommend creating a single line script using the Import Records script step, set the mapping up in it, which should be retained. If you’re doing other imports, then set up scripts for them as well.

2 Likes
10

My experience and practice of importing matches Andy’s.

11

Hi @samfmp
Just tested the file you provided. Importing from table2 into table1 works fine. As @AndyHibbs and @Cecile mentioned, calculated fields cannot be targets of import, as you cannot edit their content.

If this did not solve your problem, please provide a set-by-step-description of your import task, source and target with true field names for a more detailed look into the issue.

13

It seems that I have figured out the reason.

Firstly I have to use the filename as the path, but not a variable, e.g. file: XXX.fmp12
Then, I could set up the field mapping of importing.
Finally, I could switch to the variable path.

1 Like
14

I have similar problem again. How could I save the mapping for data source as variables with temporary path?:cry:

15

If you have the same import, again, a JDBC program could be all you need.

Unfortunately, FMP does not try to infer data types during imports as, say, the free MySQL does, but with a simple JDBC prograrm, you’ve already got those mapped.

1 Like
16

From memory, I’ve set the import field mapping using the file name (in a script) and then later on substituted the file name for a variable.

If the variable is empty, then FileMaker can’t access the file or field names to map.

1 Like
17

Enter the list of paths like below, FileMaker will evaluate them in order, until it finds a successful one.

$path
file:/Users/myUserName/Documents/sampleFile.csv
3 Likes
18

Thank all again. I guess the problem may be due to the character set of the data, the data I have used is UTF-8 while the setting of importing records is other character set so I cannot import data to the table.