Hi, I am a new filemaker developer and I have stepped into a role running an existing database where the previous (unfortunately) created the database and made a real mess. I have several fields that only work if someone actively updates information for the current year and other odd things.
The company I work for sells retail products in trade shows and has a sales force which gets paid weekly. However, not all of the trade shows run for only 1 week. Several run multiple weeks. They also return to these shows years after year. As a result, the solution they have in place is to make a separate record for each week of a show and each year, so they can get the total sales to pay their sales people. The real problem which comes up from this, is that these shows have booth fees / travel expenses and such associated with them which is used to calculate the profit of the show. When inputting the "fees" they only get associated with 1 record, the first one for that show/this year. I have figured out how to calculate the "daily show cost" by taking all of the "fees" and dividing by the total number of days for the overall show. I can then multiply that number by the days the show is running on that week. So then if week 1 starts on Friday, that would give 3 days for that week (Fri, Sat, Sun). Week 2 could be 7 days. Week 3 the same and so on. This calculation is needed to give an accurate profit/per week for each show and helps them predict if it will be good to go back to next year.
That all being said...(sorry it's kinda long winded) I would ideally love to figure out how to grab that daily cost for week 1 and apply it to week 2, week 3 and so on. I had thought of using a button in weeks 2+ to call a script which could grab that number for me, but I can't figure out how to input it into the current record. Also, what happens if the dates of the show change?
There are multiple tables in this database but the main ones are MasterShowList which holds the specific data about that trade show and the Showlist which holds the records for each show/week/year.
My other concern is that fees will change each year so I can't set the fee once and forget it which is why I thought the best place for it is in week 1 of that show.