Hello, Kathy. Welcome.
What you want can be done relatively easily using scripts. Here's how I would do it:
1) Create a relationship between the table holding the unsold paintings and the one for the sold paintings. You can use the unique key field (ID) as the match field.
2) Allow the relationship to create records in the history side.
3) Create a script that follows these basic steps:
- Check to see if the record already exists in the Sold table. (If it does, you have an error someplace.)
- If the record doesn't already exist, simply use a series of Set Field commands to push the data into the Sold table, such as:
Set Field [ Sold::Artist ; Value: Current::Artist ]
Set Field [ Sold::Size ; Value: Current::Size ]
where "Sold" and "Current" represent the names of the tables for sold and current paintings.
- Verify that the data have successfully been created via a calculation such as "not IsEmpty ( Sold::Artist )". Or, alternatively, use a concatenated field that's a joining of all the fields you're pushing over, such as Artist & Size & Description. Then you can just ask if Sold::concatField = Current::concatField. (Or, you can just do that as part of your comparison by setting variables.)
- Delete the record in the Current table.
If I may ask, what's the rationale for pushing the data to another table? Normally, you would keep everything in one table and just use a status field to indicate which items had been sold. Tables are cheap, relatively speaking, but splitting your data up into different tables by status creates issues when you want to report on it.
Please feel free to ask any additional questions.
I don't like moving records between tables.
IMHO a better solution would be to have a record for each art piece, and a related record for each time the piece is dealt with. Say a piece is sold to a customer, and some time later they trade it in and buy another piece. The original piece is then purchased by another customer. You could have a record for your purchase transaction (and who it was purchased from), another for the sale to the first customer, another for your purchase from the first customer, and another for the sale to the second customer.
Having separate tables for Pieces and Transactions would allow you to print the provenance for each piece that you've ever sold.
Thanks so much for the speedy reply.
The reason I wanted to create a new table is that my client didn’t even care to keep a record for sold work, since she handles such a variety. I thought there was some value to keeping the information, but didn’t want her unsold database to grow too large (since it also includes images), even if the records were hidden. So I thought a separate database might be the answer.
Being new to Filemaker it will take me a bit of work to go through the steps you laid out (with the help of lynda.com) but thanks so much for getting me started and I may need your help again if I get stuck!
The size of the database will still be the same if you move the records to a different table. Maybe even a tad bigger, since you have a whole new set of schema and indexes. Unless you plan to move them off to a completely different file (which is possible, and not entirely a bad idea).
I suggest you consider using external storage for images, especially if you’re concerned about the size of the file.
Yes, moving the records to a different file is what I would like to do, sorry if I wasn’t clear. That way my client has it as a resource if she wants to reference it, but it’s out of the daily workflow. How would I do that?
We are going to use an external folder for images once we get our system set up, but I’m sharing the file via dropbox while in development so it’s easier to use just one document for now.
Create the second file and put a TO (table occurrence) of the destination table on the primary file's Relationships Graph. Then follow the procedure I outlined.
Thanks so much for the quick reply, you have been so helpful, I will try this!
Always design your files using best principles and ignore what your client wants you to do which almost always leads to pain and disaster. We don't understand the magic a client puts into their business we just use our magic to create records for them to input various data items.
Use the idea of a sold/available radio button field and the idea mentioned above for creating an invoice related to the file. This invoice can be used to show a purchase or a sale with a similar radio button field.
Forget what the item is, that isn't important in the design of the file. Think in terms of records and creating the structure around records. The data in the records comes after your structure is correct.
Once you understand that it is the linking of files with only a few fields and using fields to create a status for the record your job will be a lot easier and your designs better.
Anyone on this list could sketch out a quick relationship graph with a handful of tables to solve your problem.
Hope to tackle this soon, thanks for pointing me in the right direction!