Hi, is it possible to create a find set from a spreadsheet? The spreadsheet will have 1 column with lots of lines or 1 line with lots of columns. It would just be looking for a number in a dataset.
I've included the demo file and the Excel spreadsheet that I used.
There is also a screenshot layout with steps.
#1 works as keywords notes
#2 as I suggested. Does NOT work. Importing the single column to match has no other columns to "import" so fails.
Can you provide a little more information?
"Spreadsheet" as in Excel? Or a table view in FileMaker? An Excel imported into FileMaker?
In which are you trying to search?
You can simply open a spreadsheet directly in FM. From that point you can interrogate the data just as you can any other FM database. I don't think you can interrogate the spreadsheet directly.
Yes it is a spreadsheet. What i am trying to do is the following.
I make payments in a bank system, in one of the bank fields I can enter a "key" = a number, so I can identify future payments with the "key" in Filemaker.
When payment is made I lookup the invoice and the book it into accounting in Filemaker.
So rather than lookup every key by typing it in, I thought, if I can find these "keys" by loading it into a find set via a spreadsheet (as i can download payments with the key into a spreadsheet from my bank system) would make it more efficient and faster. And my bank would match my accounting.
It sounds like you want to "import" the spreadsheet, but first make a query to it so that only specific data if imported. Is that correct?
You might do a temporary import (into table which can be ignored other than this temporary import. Given that the data would be IN FileMaker at that point you can search the temp table to get the data.
Otherwise you would need to see if Excel can be leveraged to get the value you desire into another worksheet in the same workbook (one Excel file). Then import only that worksheet, instead of all the data. That's an Excel question, not a FileMaker one.
sorry not good in explaining this one. I have a spreadsheet with numbers only. Excel is for me not the problem.
So the spreadsheet looks like this:
column A only "has invoice key"
What I would like to do is:
* go to my table with invoices
* go to find mode in filemaker
* then load in the invoice number field: 1234,2345,3456 (from a spreadsheet feed)
as I know how to do multiple manual ones but if i look for 100+ it is a lot of work over and over again
thanks again for your help, but I realise that this might just be nice to have and not possible :-(
thanks for your help
1. IMPORT the spreadsheet into a temporary table & relate the one column to your invoices. Go to Related Records would give you a FOUND SET.
Or (& this may backfire, so test on a copy of your data)
2. IMPORT and match the two fields in the Dialog. Use UPDATE but don’t ADD new records. You would start with all records before the import, but have a FOUND SET of just those that match.
I will try to get you a demo.
Sent from miPhone
As i have already suggested, and beverly has explained, the simplest way is to import your spreadsheet into FM, something like this:
1. Create a temp table with the fields you need
2. Import from your spreadsheet the data you need into these fields
3. Process the data from this table—for example, import only selected temp records into live records in a standing table
4. When finished with it, empty the temp table
Thanks Beverly and Keynote for your help this is great and I am sure just looking at the demo that this will be a huge timesaver
Although this question is marked as answered, you can also import Excel data (or search for it, or whatever) using a micro-service you write.
Assuming your Excel (xlsx assumed, in this case) data has these two columns:
You can then extract that data from a micro-service you write (Java, in my case here using the Apache POI libraries) to get the Excel data directly and return it---In this case, to FileMaker. But since a micro-service just uses HTTP verbs, you could return that same Excel search to Terminal or even a browser (or to any program that can issue HTTP verbs like "GET").
Below is a simple search which directly looks for a particular invoice in the spreadsheet above. In this case, I just returned both values, the invoice number and the amount.
(Since the micro-service knows how to read an xlsx file directly, no importing the spreadsheet into FMP first and then parsing it was necessary.)
Micro-services are a powerful, free (no plug-in required), alternative to first manually importing data into FMP and having to parse it there.
This approach might be too much for your particular needs, but keep it in mind as lots of options abound.
Retrieving data ...