Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 9 of 9
  1. #1
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:31 AM
    Last Active
    11-24-2017

    How to transfer data between fields

    I have been forced into using FMP 14, because of an unknown conflict between my MC OS and the FMP 11 that I have used for years. No one seems to be able to find this problem, nor offer a solution.


    The Script Writer for FMP 14 has me so confused that I have become lost.

    I am faced with a situation where I need to copy data from records in two different tables into records using a third table.

    I tried writing a script that is supposed to copy and paste fields from Tables A and B and paste them into fields from Table C, but it does not work.

    Attached is a copy of my file. Can anyone please lead me out of this mess?
    Attached Files Attached Files

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:31 AM
    Last Active
    11-24-2017
    Well, first off, as you've been told by several people several times, you should not use Copy and Paste to move data around in FileMaker. And, second, you seem to be asking the same basic question over and over again in different threads. Is it that you don't understand the answers you've been given, that you don't like the answers you've been given, don't believe the answers you've been given, or just can't find your way back to the threads you'd already started?


    Your script goes to Table A and copies the Table A::Assigned Pickup Day field. I rolled it to a record that actually had data in it (ID# 2039, Diana Ulloa) so it would have something to copy. Then your script goes to Table C and makes a new record and pastes into Assigned Pickup Day. Worked fine up to this point — I ended up with a new record containing "Monday" in the new Table C record. Then your script goes to Table B and attempts to copy the field Table B::ID# but it can't do that because Table B::ID # is not on the layout at all. You have a portal to Table A, and the field Table A::ID# is on the layout, second column from the left, but that's not the field your script is trying to copy. Table B does have an ID # field but it isn't on the layout and in order to use Copy and Paste, the target and destination fields have to be on the layouts you're trying to use them on. So no copy takes place. Then your script goes back to Table C and pastes into Table C::ID# but what pastes is "Monday", from the previous (Table A) field you copied, since it was unable to copy from Table B::ID #.

    If you could stop and explain in more detail what you're trying to do (and not how you're trying to do it) one of us can give you some more meaningful tips. I don't as of yet know why you have three tables with nearly identical fields in them in the first place, but this is no way to move data around. You have no controls in your script for what record in Table A is going to get copied to Table C, or what record in Table B either. Do you expect to do manual finds in those tables before running the script?

  3. #3
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:31 AM
    Last Active
    11-24-2017
    I must admit that I got a bit lost here, and I started without proper planning. I now see that there is no need for Table B (except that the printed view of the portal looks better).

    As I may have said previously, the purpose of the DB is to track distribution of food packages (Bags & Boxes) for a Non-Prof that serves disadvantaged families. They need Monthly and Annual reports to maintain grants that support their efforts.

    When the Daily Grid Sheet view is printed, it is given to volunteers who put a check mark in the appropriate date blocks. When the five week cells are filled in another volunteer who enters the data into the DB, then runs the script that transfers the data to Table C.

    The need for transferring data to Table C is that a history is needed to generate Monthly and yearly reports. Since some fields in Table A can change from time to time (Status, Assigned Pickup Day, Number of Adults and Children), it is necessary to have a record of each periods transactions. Each check mark in the grid view has to become a single record in Table C (showing the date, ID#, name, status, etc.).

    So, what should be the method of transferring data from the Table A entries to the Table C? If copy/paste is wrong, what method should be used? And this is the thing I having trouble with.

    I have stripped down a copy of the DB, and it is attached.

    Thanks for your patience with me.
    Attached Files Attached Files

  4. #4
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:31 AM
    Last Active
    11-24-2017
    I need more info on the monthly and yearly reports that you wish to run. I think you probably don't need Table C either, but until I have a clear sense of the report you need, I can't advise you on the best way to make the report happen.

    But meanwhile, at some point in your life, yes, you're going to want to have the skills to transfer data. Here's a modified copy of your database. I gave you two methods of moving data that are both more efficient than using Copy and Paste.
    Attached Files Attached Files

  5. #5
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:31 AM
    Last Active
    11-24-2017
    HOUSE OF YAHWEH REPORTS

    Thanks for your help. The attached files screen shots of Excel sheets showing the reports I need to generate.
    I tried to attach the Excel files, but apparently the Forum page will not allow it.

    HOY Clients are the recipients of food packages distributed weekly or monthly (depending on their classification…this is the R~E~W code)
    Active – Clients who get supplied weekly
    Emergency – Clients who get supplied monthly
    Withdrawn – Clients who are inactive and receive no distribution
    (these may rejoin the distributions at some future time)

    Food distribution is determined by Family size:
    Number in Family Boxes Bags
    Five or more 2 2
    Three or four 1 0
    One or two 0 1

    The bags are valued at $30 and boxes at $70

    Statistics are reported monthly, with weekly numbers calculated by formula

    The reasons for having the Reports & Calculations Table are:
    Family size may vary (births, deaths, etc.)
    Status (R~E~W) may change at any time
    Without a separate table, won’t the previous monthly sheets change if the family size or R~E~W status changes?
    Attached Images Attached Images
    Last edited by Jabert; 11-04-2017 at 11:46 PM.

  6. #6
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:31 AM
    Last Active
    11-24-2017
    Here is what I would design if I were hired for the task.

    • A table in which each record is the record of a family. It would not have first name, ethnicity, count of children, adutls, etc — It would have a family ID#, some identifier of the family I suppose (such as last name, although people in families often have differing last names), address perhaps (although depending on how House of Yahweh operates, it may not be necessary that all members of a family are at the same address; it's certainly true in the ordinary course of affairs for some families); and if pickup date and whehter or not home delivery is required is a feature of families rather than a feature of individuals, those fields would go there as well.

    • A separate table in which each record is the record of an individual. It would have first name, last name, date of birth (if available) or age (if no date of birth) and calculated age (unstored calc field defaulting to being based on date of birth, otherwise reflecting the age field). It would have ethnicity. It would have any special dietary info. And it would have both an individual ID# and a family ID#, the latter of which would link it to a record in the Family table mentioned above

    • A separate table for Service Units in which each record is the record of ONE person receiving ONE meal on a SPECIFIC date. It would have a calculation field getting year and month alone from the date, and another getting just the year from the date, and a third getting the Year, Month, and Week Number. It would have a field for whether or not the meal was a bag or a box. It would have a field for the meal cost (if it varies from meal to meal in ways other than just boxes versus bags; otherwise, if they're all assigned the same cost, dont' bother). It would have a field for weekly versus emergency. It would have Individual ID# linking it to the record of the individual. It would have the following as lookup fields (that look up their values automatically at the time of record creation from related tables): Family ID#, Age on that date, full name, ethnicity. It would have as an additional calc field the AgeStatus (child, adult, or over55 depending on the value of age on that date). And it would have various summary fields — some of them defined as Counts or Totals, some defined as Averages, because this is the table you're going to use to make all your reports. (You don't use a separate report table at all, you don't need one).


    You design your report as a report LAYOUT (not separate TABLE), using the table Service Units, with grouped data, grouped by Year, then by Month, then by Week. The body part will be by the Individual. You do use subtotals and totals, averages in some places and counts in others.

    FileMaker designs the layout for you when you use the New Layout dialog and select Report with the options mentioned. You then need to tweak it quite a bit to get it the way you want it. In Layout Mode you discover that what FileMaker handed you is a layout with these Parts:

    Header
    Subsummary When Sorted by Year (Leading)
    Subsummary When Sorted by Month (Leading)
    Subsummary When Sorted by Week (Leading)
    Body
    Subsummary When Sorted by Week (Trailing)
    Subsummary When Sorted by Month (Trailing)
    Subsummary When Sorted by Year (Trailing)
    Trailing Grand Summary
    Footer


    I think I would do the reports divided up the way the Excel sheets are: one report that has per-the-individual-date information on it (Emergency Food Tally Sheet) and a separate report that has per-year and per-month aggregate info on it. So, two different report layouts. For per-year / per-month one I would completely ditch the Body part and the trailing subsummary parts, first moving the subtotal summary fields up to the leading subsummary parts.

    The Excel sheets are arranged visually the way they are because of what Excel can and cannot do easily. FileMaker has a different set of things it does easily and less easily and I would want to rearrange the report visually to fit what FileMaker does most efficiently: I'd have all of the per-the-year subtotals in the same subsummary (annual value of food given, food boxes per year, food bags per year), annual totals for anything you want (such as number of families fed per the entire year or total children per the year, etc). Then it would go to the monthly level - perhaps summarizing different or additional data, but also breaking down the annual figures per the month. Then on to the weekly level, and, finally, per the family FOR that week.

    The other report really only needs a Subsummary part per the DATE. Sorts by date and for each indiv date it summarizes total people, families, adults, children, elderly, boxes, and bags.


    I can help you with this if you want to pursue it here, but it sort of means hitting your database with a wrecking ball and starting over. Your PERSONS DATA table is chock-full of family-level data and service-unit related data (including things like WEEK 5 SUBTOTAL and WEEK 3 STARTDATE and so on) that simply don't belong there, and your other table is an attenuated subset of it, and you don't have a FAMILY table and (more importantly) you don't have a Service Units table at all.

    And you have a bit of a learning curve in front of you as a consequence. You don't really seem to understand database table structure yet. (This will be a good way to learn it, but you need to expect a realistic learning timeframe, you're not going to have this ready right away).
    Last edited by AHunter3; 11-05-2017 at 08:32 AM.

  7. #7
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:31 AM
    Last Active
    11-24-2017
    Thanks for your reply. You have given me a lot to absorb...I will get back to you when in a week or two.

  8. #8
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:31 AM
    Last Active
    11-24-2017
    I have rebuilt this thing along the lines you suggested. You may notice the level of detail is not as extensive as your notes implied (this is because HOY only updates their client data once a year, also they don’t keep track of family ages etc.).

    I presently have three tables (Client Data, Grid Data and Pickup Data). You asked about the work flow, I think the outline below is what you meant.

    The script I have started is just the first iteration of the first loop of the five lines of data that I want to end up with. So there will be five lines of data for each client per pickup day, and a sheet of 40 clients would end up with 200 lines of pickup data.

    WORK FLOW
    1. On the Prep Daily Grid Sheet Page
    Create new grid sheet by pressing the NEW button
    Enter the assigned pickup day
    Enter the date for the first pickup day (fifth pickup day is displayed
    Pressing GO formats the sheet and goes to the new NEW PRINT DAILY GRID SHEET page

    2. The new grid sheet is printed and sent to the Pickup Coordinator in the pickup area.
    As clients arrive they are given their food, and the coordinator places a check mark in the
    appropriate box. The grid sheet stays in the pickuo area until all 5 weeks have been filled in

    3. When the grid sheet is completed it is sent to data input
    Data input pre-fills all the boxes on a sheet, then deletes the blanks (because there will always be far fewer blanks than checked fields). A script is activated that translates each line of grid sheet data into five entries to be used in generating monthly and yearly reports.

    I think I have this where I need it to be except I cannot get the TWO set variable fields into the LINE BY LINE layout. Can you look at script LINE BY LINE XFR and tell me what I am doing wrong?

    Thanks so much for your help.
    Attached Files Attached Files

  9. #9
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:31 AM
    Last Active
    11-24-2017
    Two things:

    a) You navigate to a Client Data layout and then you set the variable $WEEK 1 DATE to a Grid Data for Printing field value (Grid Data for Printing::Week 1 Date, to be precise). You repeat that with $WEEK 1 PICKUP which you set to Grid Data for Printing::Week 1 Pickup. Is that what you intend to be doing? The layout in question is a list view —*how will your script know which record you want to obtain data from? (Would you have already done a manual Find on that page, or selected which of the 234 records in that table you want to be the active record?) And your relationship that connects Grid Data for Printing to the Client Data table is Assigned Pickup Day = Assigned Pickup Day. What that means is that regardless of which individual Client Data record you're on, the value you're loading into your variables comes from the first record in Grid Data for Printing that happens to be from the same day of the week ("Wednesday" for example).

    If you were here in person I would ask you to physically point to the field you want to get the data from, and then point to where you intend to put it. I don't think you've specified them correctly but I don't know what you had in mind.

    b) You aren't using your variables at all after you've set them. Instead you have two Set Field commands that set fields in Pickup Data and Grid Data for Printing to related values from other tables.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Looking for a better way to transfer data
    By Jabert in forum ScriptMaker and Scripting
    Replies: 3
    Last Post: 10-30-2017, 03:03 PM
  2. ODBC Data Transfer issue
    By silez in forum FileMaker 13 - All Versions
    Replies: 2
    Last Post: 10-02-2014, 08:05 AM
  3. Been asked to transfer data via JSON - eek
    By henry.oswald in forum FileMaker Pro 11
    Replies: 1
    Last Post: 09-03-2010, 10:05 PM
  4. Transfer data from one table to another
    By blevey in forum FileMaker Pro 8.0
    Replies: 2
    Last Post: 01-11-2006, 09:30 AM
  5. transfer Mac data 3.0v4 to PC?
    By jazzbo in forum Older Versions of FileMaker Pro
    Replies: 6
    Last Post: 03-05-2002, 03:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.