1099 Information lookup

[SGordon]

I'm relatively new to FileMaker, but I have created a rather complex database for a client that holds information about doctors that they have interviewed in one table. Another related table holds individual records for each interview that was conducted which includes the date and the honorarium amount paid to that doctor.

The client wants to be able to click a button, enter the year and have the database search for interviews done during that year and, along with the doctor's contact information, show the total of honorarium payments made to that doctor.

I tried a relatively simple approach to this by creating a new table to hold the information found when the search is done, which is copied by a script. Since there is usually more than one interview per doctor this table then shows every interview and the honorarium amount paid for each one.

I was able to get the table to show a running total of the honorarium amounts for each doctor, but it looks confusing because each record adds the previous amounts together. Ideally, I would like to be able to have a table grid view that would only show each doctor once no matter how many interviews were done, and show only the full total of all honorarium amounts paid. But I haven't been able to figure out how to do this.

I've had luck with other features I've set up in this database by searching the internet or FileMaker books for examples of scripts that do similar things and then adapting the scripts to do what I need. But I haven't been able to find anything that comes close to what I'm attempting here. Any ideas would be greatly appreciated! Thank you!

Steve Gordon

Edited October 2, 2008 by Guest

[comment]

To find the doctors that were interviewed during a year, find the interviews that took place in that year, then use Go to Related Record [ Match found set ] to display the related doctors in a list/table layout of the doctors table. Or just go to a layout of doctors and search the related Interviews::Date field.

It is not clear if you then want to display the total amount paid to each doctor, or just the total for that year. If the latter, you will need another relationship between doctors and interviews, filtered by global year.

This is assuming you want all this to be displayed on screen in Browse mode - otherwise you could simply produce a report based on the found interviews, sub-summarized by doctor.

Edited October 2, 2008 by Guest
Added another method for searching.

[SGordon]

Thanks for the response. I am not having a problem displaying a list of doctors that were paid an honorarium during the year. The problem is that some doctors were paid more than one honorarium during the year and I need to display a total for each doctor for that year.

Basically I now have two problems -- the first problem is that I haven't figured out how to display a doctor's name only once if they were paid more than one honorarium. The second problem is that I'm not sure how to display the total honorarium for each doctor no matter how many times they were paid. I tried creating a summary field but I can't get it to display correctly. I'm not sure what I'm doing wrong. I'm rather new at this and I know there are often many ways to get a result but in this case I haven't found a way to do this that gives me what I need.

Edited November 3, 2008 by Guest

[comment]

the first problem is that I haven't figured out how to display a doctor's name only once if they were paid more than one honorarium.

For a report, you'd sort and sub-summarize the interviews by doctor. In your layout, delete the body part and keep only the sub-summary. Place the doctor's name and your summary field (total of honorarium) in this part. That should take care of your second problem as well.

As I said, this will work for a printed/previewed report. The other method would require you to create a new relationship between doctors and interviews. Then you could be browsing the doctors table, with each record showing the total sum received during the year entered into a global field.

[SGordon]

OK, I'm getting closer. I now have a report that shows the doctor's name only once. But I'm still having a problem displaying the total honorarium amount because my summary field is totalling all of the honorarium amounts no matter what year they were paid in.

The table with the interviews contains individual records for each interview, each with an honorarium amount and the year. Since the table contains records for several different years, it's adding them all together.

I need to be able to list the total for each year or be able to do a search for a particular year and show just the total for each doctor for that year. I'm confused about how to set this up. I'm sure I'm making this more complicated than it needs to be, so any further help would be greatly appreciated.

[comment]

Well, you can start by finding the records for the year of the report. Or you could add another level of sub-summary by year - it really depends on what you want to get as the result.

[LaRetta]

Well, you can start by finding the records for the year of the report.

I hope I'm forgiven for stepping in ... for 1099 you would indeed find the year; multiple years would mean nothing. You can actually use a global to hold the year for the search.

You can even produce the 1099's themselves. There are 3 per page (usually). They come with 3 ncr (carbons) but they are not required (computer copies are also accepted as long as IRS gets their copy, indicating whether the particular 1099 was voided). This means you can scan the image or IRS also allows download of single-page images. It would be cool to group by x numbers for this form - Michael could help you there. I jumped in because I've done a lot of these but always had to search by 3's. This was in vs. 6 days and before GetNthRecord() and other wonderful functions.

Producing 1099s is a common need in most businesses; it'd be cool to have a good process available for anyone who reads this thread and we are coming up on the end of the year!

Edited November 3, 2008 by Guest
Inserted sentence

[comment]

It's good you jumped in - I haven't even noticed the title of the thread.

[LaRetta]

I've been out of tax accounting and such for awhile so I thought I'd better quickly see what was happening there ... and the IRS has changed 1099s to single forms (YAY)! It seems we can no longer scan our own either; they now require scanning a form they send us. These changes mean 1) no longer will we be required to submit a cover sheet, saying how many good 1099s are included, how many voids etc. (very good deal) and 2) printing one page per customer will be much easier overall!

I should have researched this before I spoke up!

[SGordon]

I have tried many ways of setting up a report to display the information that I need for 1099 reporting and nothing seems to work. My client doesn't need to create 1099 forms from the database, they just want to be able to print a report that will display the total honorarium paid to each doctor. There is something that I'm just not getting in setting up the report.

I found an example of a database posted by Kevin Frank for someone else that gives an example of displaying student donations that kind of fits what I'm trying to do. However, when I tried to set up my database to something similar I couldn't figure out what fields were used to generate the report, so that didn't work for me either.

I'm very new to using FileMaker and I've learned most of what I've learned by trial and error, tutorials and examples that I've found on the internet. But I haven't been able to find anything that I can adapt for this. I'm not sure exactly how to do what you've suggested in your previous posts. Can you somehow give me an example? I really appreciate any help you can give me. Thanks!