In order for FM to set the SQL::Author name field, you need a relationship from that table to the Author table. So, you have a context issue.
How are these tables related? How does FM "see" the SQL table, thru ESS?
There is no "relationship" because SQL has zero records. I am trying to create the file by extracting information from Author. However I have set up a relationship which has no effect.
What is ESS?
I should have also noted: I created a global variable "found count" in Author and set its value as Get(Foundcount).
Set Field (SQL::record count; Author::found count) works.
Let's simplify the question:
You can have many records in Author Entry, each with different Author Names. When you have SQL open, how do you know which author name you want?
(There are many ways to pull data from one FileMaker file into another, which one is correct for you depends on details you have yet to share about what you are trying to accomplish here. In fact, SQL and Author Entry can reside in the same file instead of separate files as you've described.)