Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How to transfer data between fields


Jabert
 Share

Recommended Posts

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?

DB 11:1 copy.fmp12.zip

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

DB 11:3.fmp12.zip

Link to comment
Share on other sites

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.

no more CopyPaste.zip

Link to comment
Share on other sites

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?

2.jpeg

1.jpeg

Edited by Jabert
Link to comment
Share on other sites

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

Edited by AHunter3
Link to comment
Share on other sites

  • 2 weeks later...

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.

11:18.fmp12.zip

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Thanks for your replay, I have it working now but I have a new problem.

 

The script creates a new line by line record, and fills in the fields properly. But, when it goes on to create the next record a message comes up asking if I want to save the changes to the first record.

 

Go to Layout [ “DAILY GRID SHEETS DATA INPUT” (CLIENT DATA) ]

Set Variable [ $ID #; Value:CLIENT DATA::ID # ]

Set Variable [ $WEEK 1 PICKUP; Value:CLIENT DATA::WEEK 1 PICKUP ]

Set Variable [ $WEEK 2 PICKUP; Value:CLIENT DATA::WEEK 2 PICKUP ]

Set Variable [ $WEEK 1 DATE; Value:CLIENT DATA::WEEK 1 DATE ]

Set Variable [ $WEEK 2 DATE; Value:CLIENT DATA::WEEK 2 DATE ]

Go to Record/Request/Page

[ First ]

Go to Layout [ “LINE BY LINE DATA” (LINE BY LINE) ]

New Record/Request

Set Field[ LINE BY LINE::ID#; $ID # ]

Set Field [ LINE BY LINE::PICKUP DATE; $WEEK 1 Date ]

Set Field [ LINE BY LINE::WEEK 1 PICKUP; $WEEK 1 PICKUP ]

New Record/Request (THIS IS WHERE IT ASKS TO SAVE THE FIRST RECORD)

Set Field[ LINE BY LINE::ID#; $ID # ]

Set Field [ LINE BY LINE::PICKUP DATE; $WEEK 2 Date ]

Set Field [ LINE BY LINE::WEEK 1 PICKUP; $WEEK 2 PICKUP ]

New Record/Request

ETC.

 

 

 

 

 

 

 

How do I suppress the message and save the previous record without any dialog?

Link to comment
Share on other sites

Sorry to bother you, butI have a new problem.

 

I cannot get date fields to display in the header of one of my layouts. The layout DAILY GRID FOR PRINTING is supposed to generate a page for printing with Date fields in the header.

 

The sequence is (1) press NEW, (2) enter a date for the start of the period, (3) pressing GO finds the relevant records, then goes to the page to be printed. This activates the script GRID SHEET FOIR PRINTING.

 

All of this works well, except that the dates do not show in the date fields (the five fields on the left hand end of the header), The only field that is needed is the WEEK 1 DATE (the first of the five in the header) because the other fields are calculations from WEEK 1 DATE. I can manually enter the date, and then the other fields fill in, but if I click on any other field all the date fields disappear.

 

I have tried copy and paste, set field, and set variable/set field and nothing seems to work.

 

Can you tell me where I have gone wrong?

 

Thanks, I truly appreciate your help.

HoY DB.fmp12.zip

Link to comment
Share on other sites

Some of your records (Leidv Barbon, Ethel Blaha, Lorraine Brown) have dates in their records; some (Denis Bernard, Luisa Burgeno, everybody who comes after Ku Campos) do not. I will examine your script to see why the other records (all the records after Ku Campos) don't have dates, but first let's make sure you're clear on what's happening display-wise:

 

When you click on a record and make it the active record, that record is the source for any data that appears in the header up top. You don't see dates in the individual records because those are a different set of fields -- you have WEEK 1 PICKUP, WEEK 2 PICKUP (etc) in the Body part.

 

See if this makes is clearer:

 

Daily%20Grid%20for%20Printing.jpg

Link to comment
Share on other sites

OK, your script, "GRID SHEET FOR PRINTING", is a mess, and your entire workflow from the clicking of "NEW" on the "CREATE A NEW GRID SHEET" layout, doesn't make much sense.

 

 

Here's what you're actually doing in your workflow:

 

 

Clicking "NEW" makes a brand new blank record in the table GRID DATA

 

Below that you have the field WEEK 1 DATE and the instruction to "ENTER THE MONTH, DAY AND YEAR HERE:". So when a person does that, they have just entered a date (I put "1/1/1980", just for the heck of it) in an otherwise completely empty record.

 

Below that it says "this will generate a grid sheet for:" followed by the field "ASSIGNED PICKUP DATE". It's a calculation field and it correctly reports 1/1/1980 as "TUESDAY"

 

Below that is the "GO" button which runs the script "GRID SHEET FOR PRINTING"

 

which does this:

 

Copies the ASSIGNED PICKUP DAY ("TUESDAY" in this case). Keep track of the fact that this is what has been copied to the clipboard.

 

Goes to the Client Data layout

Goes into Find Mode

Pastes from the clipboard into Assigned Pickup Day

Performs the Find. (I now have a found set of everyone in Client Data who has a pickup day of "TUESDAY")

The active record is the first (topmost) record in that found set — the oldest TUESDAY record in your table. That's important as you'll see below

Sorts the data (no dialog). The active record is still the SAME record although it is no longer the FIRST record because we just sorted them.

Goes back to CREATE A NEW GRID SHEET and doesn't do anything at all

Immediately goes to DAILY GRID SHEET FOR PRINTING which is a Client Data layout so we're again on the found set of "TUESDAY" records, sorted

Enters Browse Mode, which we're already in at this point

 

Here's where the wheels really fall off:

 

Goes to the field WEEK 1 DATE. The active record is the record that was originally the first record in the found set of TUESDAY records, before we sorted them.

Attempts to PASTE into WEEK 1 DATE. That's not possible because WEEK 1 DATE is not in the body on this layout. Unlike Set Field, the Paste command requires that the field be on the layout. I may not have mentioned that in a List View or Table View, the field has to be in the Body part of the layout.

 

The value it would paste if it COULD paste is the word "TUESDAY" — that's the last thing that was ever copied.

If the field were in the Body part so that pasting was possible, FileMaker would still not let you do this because "TUESDAY" is not a date and WEEK 1 DATE is a date field.

 

Then it goes to the first record, blanks out the field WEEK 1 PICKUP, and uses Replace Field Contents to blank out all the records in the current found set in that fields.

 

Does the same for WEEK 2 PICKUP and the other WEEK x PICKUP fields.

Link to comment
Share on other sites

I guess I wasn't clear enough. The date fields I referred to are in the header. The field you though were dates are only single number fields. So the records I am trying to send to the Line by Line layout consist of the data in the ID#, Bane, etc. with a date field equal to the date in the Header fields (each persons entry will generate 5 records in the Line by Line layout, one for each of the five dates in the Header. My problem is that the dates in the Header are not always there.

 

Please generate a sheet from the Create Grid Sheet layoutusing the date of 11/22/17. Now look at the dates in the Header They may be blank or have dates ,n them. Now click in any of the Body fields and the Header dates disappear. Clicking around radomlywill sometimes bring the dates in th Header back, but the disappear with next click in any other field in the Body. I need the Header dates to be stable because they are the ones that are used with each Line by Line entry.

 

The best way to illustrate this is to click in the 5th data block (right end) of the 5th record (Luisa Burgeno) and the Header dates disappear. Next click on the 5th data block for record 6 (Ku Campos) and the dates reappear.

 

You will also notice that the dates below the titlle (in red) at the top of Header appear and disappear in sync with the change in the 5 date fields/

Link to comment
Share on other sites

Now click in any of the Body fields and the Header dates disappear. Clicking around radomlywill sometimes bring the dates in th Header back, but the disappear with next click in any other field in the Body. I need the Header dates to be stable because they are the ones that are used with each Line by Line entry.

 

Yes, I explained this in my own post — both that this happens, and why it happens.

 

You are trying to paste into a field that is not in the body part of the layout, and the thing that's on the clipboard at the time is a day name (like "Tuesday") not a date.

 

Reread post #14 above.

Link to comment
Share on other sites

I overlooked your instruction on having these fields in the body, but I am still having problems here.

 

I put the fields in the body, but only 9 records have dates in those fields. If you would open the attached file, and go to the layout DAILY GRID FOR PRINTING, you will see those records. For the life of me I cannot see why the other 239 records don’t show these dat fields.

 

I tried duplicating record 1115 of the favored 9, and it duplicated with a new ID# and all of the date fields showing.

 

Can you please take a look at the attachment and see if you can detect why these records are different from the others?

 

Thanks again.

HoY DB.fmp12.zip

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use