Copying records in a tablefm12
Posted 02 July 2014 - 07:17 PM
Any help would be appreciated.
Lifelong FM Student
Posted 02 July 2014 - 08:52 PM
If you have proper relational structure, and unfortunately it doesn't really sound like you do, then a combination of static and unstored aggregate works best (static figures up through month end or through the day) and then to keep dynamically current, aggregate only a narrow current relationship.
'has a bunch of other fields that basically tell me how much inventory' is concerning and suggests you do not have correct structure. There also are different approaches to inventory - some suggest just changing a figure in the Product but then you lose ability to track what happened. In all, seeing your file or an example of your structure would be necessary. And what are those 'bunch of other fields' and what table are they in? Inventory is usually a more advanced design so writing single number to Product table IS easier if new to FM but I still suggest doing it right with a true Inventory table.
And welcome to FMForums!!
Posted 04 July 2014 - 05:34 PM
Lifelong FM Student
Posted 05 July 2014 - 01:21 PM
Well, your screenshot helps to confirm that you are not structured properly but unfortunately it does not help me help you solve it. I do not know anything about Lottery but it seems that you are purchasing a large number of tickets? And these tickets can be in books and you then sell tickets from these books and you want to track how many tickets (of each type of Lottery and Game) are still available for sale, probably also tracking your proceeds. is this right?
Is it correct that a 'game' only happens once and is on a specific date? Or does a game happen for many dates in a row? Showing only this table, do you have any other tables involved in this solution, such as a Clients table to hold names and phone/email of people who purchased? So please think along these lines ... looking at each type of information as an entity (games, game number, books, tickets in a book, dates of a lottery ... and are these entities a one-to-many relationship. Questions would look like this and please correct/add information as needed:
- One book holds many tickets
- A client can purchase one or many tickets out of a book
- A Game Name can have many Game Numbers, i.e. same game can be played many times
- A Game Number only happens once ... on a specific date
Since you use the word 'sold', I will make an assumption that you are dealing with Client money so tracking the client and method of payment
might should be important. All I can tell you at this point is that you are missing at minimum a Products table and probably Clients, Invoices and Inventory; it seems the table I am viewing would might be the LineItems table.
If you can explain further by walking us through your actions (as if you were explaining to someone who needed to take over handling your business), we could probably help further.
Posted 08 July 2014 - 11:53 AM
I created a database in access approximately 4 years ago and it works great. I wanted to convert it to FileMaker so I could use my iPhone or iPad to input the ending inventory and calculate how many books I need to order for the upcoming week.
Hope this sorta explains what I want he program to do.
Lifelong FM Student
Posted 08 July 2014 - 03:52 PM
Yes it does explain it better but I won't be able to address it tonight. If someone else hasn't assisted, I will provide what I think you need tomorrow.
My sense that a summary might do the job very simply but ... well, I want to look again tomorrow now that it is clearer and when I am fresh. Several questions come to mind now ... 1) does each type of game hold a different number of tickets in a book, 2) do people buy books or, if a book has 50 tickets, are you okay if it tells you there are 50 tickets in this game's book and you enter 50 when you sell a book and 2) do you track the DATE when a new game comes out so you know when it expires?
Would you like to be able to specify that you want to keep IN STOCK:
10 Crossword books which contains 50 tickets = 500
25 Buzzard books which contains 10 tickets = 250
15 Big 10 books which contains 100 tickets = 1500
So whenever you push a button, it tells you that you need to order
5 Crossword books
3 Buzzard books
10 Big 10's
Every time a book or ticket is sold, will you be entering that sale into FM from iPad or iPhone? If partial books are remaining, do you want to be told to round UP or round down, i.e., if you sell 5 Crossword books plus 2 tickets from the 6th book, do you want to reorder 5 or 6 Crossword books?
Okay, the questions kept coming but I truly must leave now. I shall check in the morning to see how this thread has progressed for you. We will get you there!!
EDITED: Corrected a sentence
Edited by LaRetta, 08 July 2014 - 03:55 PM.
Posted 08 July 2014 - 11:28 PM
On Monday when I get ready to leave, I take an inventory of all of my lottery games. I count the number of books I have for any given game plus I add in all the partial books. For accurate sales, i convert all full books into individual tickets so that way I can add the partial books in. For instance, a $2 crossword game has 150 tickets per book. If I have 10 full books and a partial book of 100 tickets, I would have a total of 1600 tickets. In my program, I would enter 10 in the full field (see screen print from the other day) and 100 in the partial field. I would repeat the process for each of the lottery games.
Based on the sales for the last couple of weeks, the program would calculate how many books I need to order for the week. When I get a new delivery of lottery books, I would need the program to copy the ending inventory I had when I did my inventory on Monday and add in the delivery I just received. This is the part I need help with. How do I get FileMaker to copy this ending inventory and create a new records with the current date. If I wasn't using FileMaker go it would be relatively easy- I could just export the data into a text file, import it into a new table, move the ending inventory field to the beginning inventory field and then move the records into the weekly numbers table. But FMgo will not allow you to import. So I need a way to do this.
I tried using duplicate record, but that only copied one record. I was able to move the ending inventory to the beginning inventory using that command but the rest of the records where ignored.
Lifelong FM Student
Posted 09 July 2014 - 11:42 AM
So do you track the expiration? This (I think) might be important so you know you have 2 books at the old expiration and don't sell them. And FIFO (first in first out) would come into play - you want to move the older game numbers first. So when you get new books, do you record their expiration? This is also important to note because if a game expires and you are left with unsellable tickets then those are a business write-off and should be coded as such so when your accountant wants to know your losses, you can simply give them a total.
Based on the sales for the last couple of weeks, the program would calculate how many books I need to order for the week.
So if you sold 10 books plus 5 tickets, do you want the system to tell to order 10 books or 11 books? You can't order partial books - or can you? It is also important that the expiration be tracked because if five of those books left in stock were expired, you would be deceived into thinking you had enough books and you wouldn't.
A good solution will take care of all of this for you - which is why I am asking - I am gathering requirements so I can provide you a small logical demo.
And one more thing ... do you make a profit on these tickets, i.e., do you have a Retail Price and a Wholesale Price (the difference being your profit)? That difference can also be handed to your accountant. :-)
Posted 10 July 2014 - 06:08 AM
As a general rule, when purchasing items in the retail world, you order 1.5% of your sales. So if I sold 10 crossword books, I would order 15 to allow for a growth in sales. In the case of a fraction of books to order, I error on the side of caution and order up.
Lifelong FM Student
Posted 15 July 2014 - 07:27 PM
Selling that many tickets a week, I was still unclear whether you entered tickets as they are sold sold or whether you entered a full amount of a single 'sale'. But this is how I would handle inventory where I could write static the back values and keep current sales using aggregate for dynamic current totals. It also shows how to generate a report to order more tickets - you may wish to export. And then it shows how to write those purchases into Inventory as a purchase.
It also shows how to update the 'freeze date' and frozen balance to bring your inventory balance forward. Note that a table occurrence of Inventory filters down according to this freeze date. A combination of static data and dynamic aggregate provides the best combination.
I didn't catch the 1.5% increase but you can adjust that easily. :-)
Also tagged with one or more of these keywords: fm12
The Presentation Layer →
Brain Food →
Relational Database Theory →
FileMaker 13 Platform →
FileMaker 13 General Discussion →
FileMaker Platform →
FileMaker Pro 12 →
Database Schema & Business Logic →