Jump to content

Copying records in a table

This topic is 2573 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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 by LaRetta
Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

This topic is 2573 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By DPaquin
      I do want to upgrade from Filemaker Server 12 to version 16.
      At first I've wanted to uninstall v12 but cannot access to the console. 
      I get screen saying "Cannot start the Consol, managing  Admin Server not available?
      Does anyone have an idea of why I cannot start the FMS 12 Consol.
    • By candell
      FYI I tried to add this to the Filemaker community page but don't think it worked hence adding here also.
      I have been tasked with creating a website for a charity, part of the website will be a searchable archive of all of their historic data. This data is stored in Filemaker Pro 12 on the historians machine.
      He has provided me with 4 .fmp12 files, I downloaded a trial version that I found here http://www.filemaker.com/redirects/ss.html?file=PRO12MAC however none of his files will open.
      The website runs on Wordpress which is php based, the database is a maria database although I am happy to use whatever method I need to get this data on to the website and searchable.
      I was expecting to open the files in the trial version, export as csv and create and import in to the maria database.
      Is this possible with filemaker, do you think I would be better downloading a trial of the latest version, will his files open ok in the latest version?
      Unfortunately I have no experience of filemaker so am struggling a little.
      Thanks for any advice anyone may be able to provide.
    • By JH Howland
      Filemaker Pro Adv 12 running under Windows 10.
      Trying to "Send Mail" with two (2) attachments.  The specified attachment files are defined as $Path1 (from_map.png) and $Path2 (to_map.png).  Both files are located at C:/Users/{user id}/AppData/Local/Temp/S<n>.
      The Specified files are entered as:
      However, when Send Mail is activated only $PATH1 is shown as an attachment.
      Can't seem to get past this problem, help please.
    • By Kishan_Canoo
      I would like to have options to enter either fields (selling price or margin) and the other should be automatically calculated for instance:
           Cost Price = 100 (Available)
           Sell Price  =  Calculation field or ?? Input amount
           Margin     =  Calculation field or ?? Input percentage
           If i put 160 on sell price it should automatically put 37.5% in margin or if i put 37.5% in margin field it should automatically fill sell price for 160.
      File uploaded
      Login name: admin
      No Password: 
      Thank you in advanced.
    • By John Link
      I have FileMaker Pro 12 installed on four computers on my network, and FileMaker Pro Advanced 12 stored on a fifth computer. There is a shared database stored on one of the first four computers. When using the shared database from the fifth computer and I attempt to check any of the checkboxes for the field Events, I see the message shown in the attachment and I am unable to check the checkbox (This behavior occurs only on the fifth computer). If I then do a search, after doing the search I am able to check any of the checkboxes in the previously problematic field. I've restarted the fifth computer and the problematic behavior stills occurs.

      Any suggestions?

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.