Calculated field to equal specific field from specific record?
Hi everyone. I'm really enjoying filemaker and have done some fairly complex stuff but I am totally stumped about this next project. I think I might need to use variables and expressions but I have no experience with those, and I just can't seem to get it to work. Here's what I need to do:
I have a table "Reports" and it has numerous fields. The issue is with the number of the report, which is based on our business project number. The second part of the report number is sequential beginning with 1. I'm pretty sure I'm on the right track with making 3 fields: My project number which is assigned as a member of a value list from another file that stores all of my project information, say "Project", my report number, which is the sequential part, so "ReportID", and "k_ReportNumber", which would be a unique calculated field (Project & "-" & ReportID).
The problem is this:
Each project will have multiple reports. How do I calculate the ReportID so that it will check if any reports exist for that project already, and if they don't, set the field as "1", and if they do, get the most recently created report and add one to it so I can do the next one?
The second problem is a related issue:
I will also need to copy certain fields from the previous report for that job to different fields in the new/current report. The issue is that when using GetNthRecord or something like that, the previous or last report may not be for the same job.
For example, if I create a new record and select "Project A" from my dropdown list and no other records exist for this project, ReportID will automatically be set to "1" and ReportNumber will be the unique value "Project A - 1". At a later time, if I create a new record and select "Project A" again, the ReportID will be set to "2" an ReportNumber "Project A - 2". In the second scenario, I will also need those fields to be copied from the most recent/last report to the new report, but in different fields. I.E., if 2 additional fields exist on and on "Project A - 1" the field "PreviousAmount" is "0.00" (which will also need to be automatically entered or left blank if no previous reports exist), and "CurrentAmount" when I create the record with the ReportNumber "Project A - 2", I need the "PreviousAmount" field to automatically be set to the "CurrentAmount" from the most recent report/greated ReportID for that specific project.
I'm having a hard time trying to figure out how to get the calculated fields to do calculations based ONLY on the most recent/highest ReportID record that is for the same project.
I run into issues because the ReportID and Project will both occur multiple times, and only the ReportNumber will be a unique value.
This was really hard to explain but hopefully it makes sense! I think they should be similar issues to solve. I am SO stuck. ANY guidance is GREATLY appreciated.