Sign in to follow this  
Followers 0

Copying records in a table

11 posts in this topic

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.

Share this post

Link to post
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:

1 person likes this

Share this post

Link to post
Share on other sites

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


Share this post

Link to post
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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
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

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
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. :-)

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
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.  :-)

Share this post

Link to post
Share on other sites

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
Sign in to follow this  
Followers 0

  • Similar Content

    • Select Entire Column
      By AngelArs
      This should be a easy one to answer...
      Recently imported an excel file and all of the formatting was off. 
      Looking at the table, I need to select everything in a field (column) so that I can center every entry in the column. How do you do this? 
      I tried selecting the top entry, hold down the shift key and then select the bottom entry, but that didn't work.
    • Lost Data FileMaker Go 12
      By CaseyJustice
      I'm using FileMaker Go version 12.0.8 for iPad to run a database created using FileMaker Pro 12 Advanced. After collecting several days worth of data and backing up daily with no problems, I suddenly lost a day's worth of data while attempting to transfer the data to my laptop via iTunes. I could see all of the data on the iPad, but after trying to save to the laptop, all of the most recent data I collected disappeared. It's as if the most recent data was never saved on the iPad and was lost during transfer to the laptop. The only thing I can think of that might be related to this problem was an update to the iPad operating system (to version 8.4 (12H143)) that I performed prior to the last day of data collection. Now I am very nervous about using FileMaker Go to collect additional data for fear that the data will be lost. Anyone have any thought about what may have caused this or how to prevent it in the future? Thanks very much.
    • Printing X number of labels, where X is calculated in a field
      By luxpiro
      I need to print labels for items (products), and the number of labels (copies) for each product is calculated in a field of the database.
      Each record calls for a number of items to be labeled with specific instructions for packing (date, location, etc.).
      For example:
      Record 1 calls for 3 items type no.1
      Record 2 calls for 1 item type no.2
      Record 3 calls for 7 items type no. 5, and so on...
      I have duplicated the number of records in a separate database just for printing, but is so tedious (I have to process one record at a time) and is error sensitive.
      The databases I work with, generally have between 200 and sometimes up to 2,000 records (or more) per database. And each one can generate from 200 to 5,000 labels (or more), that's why I'm looking for a more convenient solution.
      Any ideas are really appreciated.
    • Portal not behaving (as I think it should)
      By BergsHM
      Hi! I am new to Filemaker, but have done several hours of tutorials and was pretty confident when I started to build a little portal yesterday. Essentially, my portal needs to act as a portal on an Invoice layout, where the user can add a certain quanity of specified products to a invoice list, which is then summed at the bottom.
      My first try did not work, but I found a fantastic little example Filemaker Pro file that seemed to work perfectly (admittedly, it was Filemaker 7), but its portal included fields that could be edited with the number of the product, which then populated the price further along on the line, and the user was expected to enter the quantity.
      What I need to do is exactly the same, but with materials and components. I think the relationships are the same and I have been very careful in setting them up with a join table between materials and components. But even when I COPY the portal from the example file to my own file, something gets lost and the nice little fields that can be entered data into are not visible in Browse mode (even after I change the table references to the appropriate name).
      Would someone mind please having a look at the example portal on the Invoice layout of the attached file, and telling me what on earth I am doing wrong? I have been meticulous in checking that the settings are consistent, but I must have missed something. 
      (Ok, now this system won´t let me upload the fmp12 file, despite the fact that it is only 246KB in size.) I will post this message and then see if I can figure out what the problem is with the upload...
    • Need help with an if statement that is not working as I think it should!
      By s_te_ph
      I am sure my if statement should execute as 'true', but it is not.
      I am on a table called SALESINVOICES. I want to check how many SALESINVOICELINES (ie, the products attached to the invoice) qualify for a special.
      My 'If' statement is:
      $CorS = "C" and ( SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID = SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount )
      where SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID is a summary field counting the total of lines that have flagged as qualifying for the special, and
      SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount the found count of products that qualify for the special, and
      $CorS is a variable that shows whether a store is a customer or shipping store.
      Before this If statement runs, I put a Show Custom Dialog to check whether the fields are definitely giving me what I expect of them.
      The dialog box shows:
      $CorS & "      " & SalesInvoices_SALESINVOICELINES|CompanyID|fqualifiestoptenspecial::s unique no products count customer ID & "       " & Count ( SalesInvoices_PRODUCTS|toptenproducts::a_c1 ) &     "      " & SalesInvoices_PRODUCTS|toptenproducts::z_FoundCount
      and the output is:
      C   12    12    12
      So I believe the If statement on the following line should execute, as $CorS = "C", and the other two fields are identical.
      Is there some other problem - will it not work out the summary field correctly in the If statement?
      Any help would be appreciated, it is driving me crazy!