I need to merge data from numerous records into one record. There is a unique identifier for each record with a common identifier for groups of records. What's the best way to merge? Is there a script available? Thanks for your help.
Can you post an example of what you mean "merge"? Do you need the data from several records into one record, one field? Or into one record, several fields? Bogus data is fine, screen shots always a bonus!
It would also help to explain why you need to do this. On the surface, it doesn't sound like a good idea to do this, but that's just a first impression going on very little information.
Second impression: There are a number of ways that you can combine data from multiple records and fields into a single block of text for reporting/display purposes so that may be what you are requesting here, but rather than making everyone guess, please provide more information about what it is that you have to work with and what result you want to produce.
I built a solution that enables Food Pantry clients to register on iPads each month. Solution works extremely well. The county Food Bank wanted to use the solution on a larger scale. I received an Excel spreadsheet with over 31,000 lines (each line with info on a member of the household). Common Record ids identify each member of a certain household. The reports required by the State Food Bank want the head of the house and then all info for the other members in one record. Pic 1 shows the info as it was given to me. Pic 2 shows how I need to merge the data into one record. There are only 5 unique fields I need to merge.
Is there an easy way to merge 31,000 records? Any help will be greatly appreciated. Thanks for your review. Paul
A summary report can be designed such that you get one row of data per family.
Use a sub summary part “when sorted by” that common ID. Summary fields placed in that part can compute subtotals etc based on all records for that family. Remove the body part and always sort your records by this common ID.
If you need to generate such reports frequently, you can use either a script or export grouped data to then import to produce a table with one record per family and these subtotals stored in number fields.
I wish I could just generate a report but, the following form is a mandate from the State of PA. I have no option but to use their form. I'm not permitted to alter it in any way. So...I have to put the data in as I indicated unless there is some other way to do this. Ive also attached a completed form to show the merged data. Thanks for your assistance. Paul
I might be wrong but if you import the Excel records into a table "Family Members", base the mandatory form of this particular table and relate to the parent table for the Address.
It would be the same organization as an Invoice system with:
Invoice -----< Invoice Line Items where Invoice::ID_pk matches Invoice Line Items::Invoice::ID_fk so in your case
Family ------<Family members (suggestion)
Yep, you aren't merging this data at all as far as I can see from what's been provided.
You can import this data twice, once into a Head of household or "family" table where the "commonID" has Unique values, validate always specified and thus you'll only get one record per family in it. Then import all the data into "family members" table and as ThierryGuemboura has already suggested, build the above report layout on a layout based on the family members table.
Retrieving data ...