You could achieve this by "de-duplicating" records, based on the combination of Product/Description/Manufacturer.
So, what you can do is create a new calculation field that appends these three fields into the one, then running a script that will remove the duplicate records.
The script would be something like this :
Sort (newfield-calculation of the three fields added together)
Goto record first
set variable $_info to the calc field
goto record next (exit after last)
if ($_info=current calc field)
Omit or Delete the record
Thank you both for your replies.
Would you still recommend the same solution if:
a) this is going to be setup as a recurring report? (I'm taking data from a rental inventory database and creating product forms that are usable for the sales and operations staff. The export from the internal database will occur daily or as needed)
b) there are more than 60 fields in the excel spreadsheet I'm importing?
Please let me know if you need more information to understand what it is I am doing. I tend to be a bit vague because I am such a noob in the world of data and FMP.
What is your end goal here? Are you truly wanting to remove duplicates from the data set? Do you want to create a value list with those three elements? Are you trying to set up a list of unique values in a separate table for comparison against an inventory? Or do you just want to create a report?
We might be able to recommend a method if we knew what the actual end state goal was.
What is your end goal here?
I want to create a product brochure like this:
From an excel spreadsheet like tihs:
Are you truly wanting to remove duplicates from the data set?
Not necessarily, I'm trying to create a product list (1 product) from an asset list (quantities of 1 product). It would be much easier if I had a parent table of Product IDs but I'm trying to minimize the interaction between export out of the R2 database and import to FMP since this could be a daily recurring import. I'm thinking daily imports so I can track asset locations with accuracy.
Do you want to create a value list with those three elements? Are you trying to set up a list of unique values in a separate table for comparison against an inventory? Or do you just want to create a report?
I don't think I'm shooting for a value list. From what I can tell I don't think that's what I'm going for.
Ultimately, this will be a read-only database since I won't be creating anything but layouts with FMP, the data lives in R2.
Does that help clarify the purpose?
Thanks for your time.
Not sure if you'll be able to get exactly the same layout style, but you should be able to come close using the technique I mentioned to get the "short" list of products, then simply create a label layout for the products to dislplay. I'm sure there are other ways of acheiving the same result, but I think this would be the simplest.
Omitting the "duplicates" from the list is step 1, then printing/pdf-ing the list from a label layout is the second part.
Peter's looping method would work fine. You could also build a value list from the concatenated field. This automatically omits the duplicates. Then, loop over the value list using the ValueListItems ( ) function and create the new records in a separate table. Or, use the Virtual List technique on the results of ValueListItems; this would likely be considerably faster.
Brian, you could simply import into the FM table as I had previously suggested. Your import table field for the "Product" should be set as "Unique value" . Try it out, you'll be amazed at how simple it is.,