Copying records in a table

[rwolanin]

What is the easy way to have a script copy a found set of records and then manipulate some data in the copied set? Specifically, I have a table that has date field and has a bunch of other fields that basically tell me how much inventory I have on that day. I then want to run a script that will pull the ending inventory on the last date in the table, copy that information and create a new record with a beginning inventory level on the new date (the date the script was executed). The catch is that this has to be able to run using FileMaker go. So importing/exporting the found set isn't an option.

Any help would be appreciated.

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Pro 12" to "Managing Scripts".

[LaRetta]

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!! 

[rwolanin]

Here is a screen print of my table.  Does this help at all?

[LaRetta]

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:

1. One book holds many tickets 
2. A client can purchase one or many tickets out of a book
3. A Game Name can have many Game Numbers, i.e. same game can be played many times
4. 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.

[rwolanin]

Actually it's for the business I manage. I manage a top selling convenience store and we sell a ton of lottery tickets. The lottery date is the date we receive the book of tickets (each book costs $300 regardless of the value of the game). I don't track who buys he tickets, but merely how many were sold in between the time I received the books (usually on a Wednesday) and when. I need to order more (the following Monday or Tuesday). The game number itself is the lottery office cross reference number for the game - for instance crossword has been around for years every year or so, the lottery office comes out with a new game number for the game. The name stays the same, but the old number turns inactive approximately 6 months after the game new game number comes out.

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.

[LaRetta]

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

... etc

 

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 July 8, 2014 by LaRetta

[rwolanin]

Let me walk you through the whole process:

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.

[LaRetta]

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. :-)

[rwolanin]

The expiration is irrelevant. I sell approximately $15-20,000 worth of tickets a week. If a game expires (rarely would we have any in stock), the lottery office gives us full credit.

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.

[LaRetta]

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.  :-)

Tickets.fmp12.zip