Some questions, thinking about your post:
What happens to the records after they move? Is this just so you can have a list of Work in Progress/Completed Projects
I would consider 1 table for starters. On layout trigger hides Completed records. Have a status field, with a value list or radio buttons to show Work in Progress or Completed Projects. Have it select the proper one via a script trigger on Start Day field and End Date field. If Start field has date and End Date is empty, change status to Work in Progress. If Start Date is populated and End date is populated, change status to Completed, hide record.
I think what you are suggesting is just a different layout, not a different Table; is that correct?
Yes, I want separate layouts for Active Inventory, Work in Progress, and Completed Projects. No record should appear in more than one list
I haven't tried scripts yet, but I get the general idea of an if/then field and will see if I can make that work tonight.
Appreciate the suggestion and thanks for taking an interest in this.
I would not think in terms of two tables. Keep all your records in the same table, but bring up a different sub set of the total records. This could be the same layout or two different layouts. There are two basic approaches to do that:
1) Perform a find--this can be scripted and the onLayoutEnter trigger can perform it, to find all records that meet the work in progress criteria or the completed projects table. And script triggers can be added so that user performed find results are automatically limted to just one group or the other.
2) Set up a portal with either a relationship or a portal filter that limits the records listed in the portal to one or the other set of your records.
By keeping your records all in one table, you keep your options open for work with your records that requires working with both on the same layout such as a summary report that lists records from both sub sets grouped by whether they are "in progress" or "completed".
Thanks for the suggestion. I'm beginning to think that perhaps a second file is actually more appropriate than different layouts or tables. The problem with keeping everything in one file is that once something moves to Work In Progress, the information is never needed in the Inventory again. I know how to save a Find and Omit a Record, etc., but I have to ask for it each time; there is really no need to have the two sets of records blended -- once the transition is made from Inventory to Work in Progress, the change is permanent. I want to see only what material can be worked with in the one, and what material is being worked on in the other.
I know how to do this manually -- I export the record to a file in Finder, delete it from the original FM file, and then import it into a different FM file -- all the data in the record is transferred, so nothing is lost (and could be moved back, if necessary), but these steps are clumsy and time-consuming. I spent some time last night trying to learn how to write a script that would do this. It seems what I need is a date trigger or button to initiate a script for it, but I'm doing something wrong because nothing I've tried has worked yet. IIf you have a quick instruction for how to write it, I'd appreciate it -- it would save me a lot of time.
Separate files and separate tables are functionally the same thing but using separate files makes for a more complex implementation.
The problem with keeping everything in one file is that once something moves to Work In Progress, the information is never needed in the Inventory again.
And why does that make keeping the data all in one table a problem? Why go to the trouble of moving this data at all? What problem does that solve for you?
but these steps are clumsy and time-consuming.
And wholly unecessary if you keep all the data in the same table.
"Why go to the trouble of moving this data at all? What problem does that solve for you?"
When referring to inventory, I only want to see what is available to use for another project. Records for Work in Progress or Completed are completely irrelevant for that purpose. Selecting a material from Inventory for a new project is one task. Monitoring a Work In Progress, or having a record of a completed project for reference is a completely different task -- they don't really relate or inform one another in any practical way once the material is taken out of inventory. The inventory contains unique items, not a flow of identical objects passing through.
A useful analogy might be antiques -- a chair is recorded in inventory when first acquired -- date, cost, source, photos, condition, etc; When a client has the chair under consideration, the record is moved out of inventory to Pending and relevant data for that phase is added -- if no sale is made, it can be placed back in Inventory, When the item is Sold, details of the transaction are added and the final record then contains complete data of the entire progress of the item from first to last, including information on whoever had it under consideration but did not purchase it..
I know how to do a Find that selects for or omits fields with something like a Start date and how to save a Find, but that command has to be repeated every time, which is a nuisance in this case -- it would be easier to just export once and be done with it. But maybe there is something I don't understand about how to permanently hide certain records in one Layout but have them appear in another?
When referring to inventory, I only want to see what is available to use for another project.
Understood, but this does not require separate tables to only see what is "available for another project". There are a number of ways that can control what records are visible/accessible on a given layout, you don't need separate tables to get that result.
OK, clearly I've overlooked something obvious; I'lll go back and do some more homework . Thanks.