Can I do this with Filemaker?

[Ian_Johnson]

 

I would like to create a program that needs the functionality of both a spreadsheet and a database. I'm hoping I can make it work with Filemaker because I know that I can use Developer to create a standalone program rather than something that requires the user to already have Excel.

 

I would like to make a program that will chart real estate data for multiple markets. The user would first create a space for the city they are interested in. Then they would enter monthly data for a particular set, like # of sales or median prices. They should be able to paste in an entire column from a spreadsheet, and not just type in data from one month at a time.

 

A formula would run on the numbers, and the results would appear on a line graph. The graph would appear alongside a form where the user could select which cities and datasets are shown, with the graph updating on the fly. A type of data could be assigned a color, ie all lines for prices would be blue. Cities would be assigned a shape for the data points. For instance, Dallas prices might be a blue line with square points, while Austin prices are blue with circles. Multiple cities and datasets could be displayed at once, or one at a time depending on what the user wants.

 

I'm trying to think of how data from a spreadsheet would translate to a table. A table could represent a city. Then a dataset could be a record with each month as a seperate field, or there could be one field for each dataset with each month being a separate record. Either way, can the user paste in values for multiple records or fields through a user interface?

 

Is there any point in pursuing this with Filemaker?

 

Ian

[harryglos]

Hi Ian,

 

RE: They should be able to paste in an entire column from a spreadsheet, and not just type in data from one month at a time.

 

You cannot do this is FileMaker, FileMaker is field specific! You could import fields from Excel into FileMaker and get it done that way, but not a cut and past of an entire column.

 

You have found the obvious Ian. A spreadsheet and database are two different animals. You can get them both to work similarly but not the same. There are Plug-ins you can get that will work as an emulator of sorts. But I’ve never seen one that really does the job your talking about.

 

Sorry to say that because we’d love to have you as part of our community. With that said, allow me to say this. I think long term, if I understand your post correctly, you will be better off with FileMaker. Excel is a great program but you will soon hit a wall and require a relational structure!

 

Harry

[Ian_Johnson]

 

Even if this one idea doesn't suit filemaker, I'm sure I'll think of something else later that will. I just wish Excel had the same ability to create a standalone application. Are you aware of any spreadsheet programs that do?

 

 

Ian

[Raymanj]

You may want to look at this plug-in yourself and decide if it will do the job.

 

xmChart plug-in.

[CobaltSky]

Hi Ian,

Harry is both right and wrong on this one.

 

True enough, if you copy a column from excel and head over to FileMaker and paste, you will likely be disappointed with the result.

 

BUT - FileMaker is a programmable environment in which you can create a script which will take the column you've copied from your spreadsheet and place it inot fields, records or wherever else you want it. The difference is that spreadsheets provide a fixed flat structure in which a limited number of operations work out-of-the-box. By contrast, FileMaker provides an open-ended environment in which an unlimited (well, nearly) number of operations can be created and made to work in whatever way you desire.

 

So, after copying your column from Excel, head on over to FileMaker and run a script called 'Paste City Column', which executes a command sequence along the lines of:

 

Paste [select; gTemp_txt]

Go to Record/Request/Page [First]

Loop

Set Field [City; LeftWords(MiddleValues(gTemp_Txt; Get(RecordNumber); 1); 9999)]

Go to Record/Request/Page [Next; Exit after last]

End Loop

 

- which will distribute the contents of the column you copied from the spreadsheet down a 'column' of 'City' fields in the current found set of the current table of your FileMaker database.

 

If you want FileMaker to distribute the data differently (eg place it into a different field or autonmatically create new records to place the values into etc) then you can adjust the script accordingly. If you require a number of different behaviors depending on what it is you are bringing across from the spreadsheet, you might want to either:

 

1. Create several scripts with functional variations, or

2. Build variations into a master script, based on a script parameter value, then provide a series of buttons which invoke the different variants by passing a control value to the script as a parameter.

 

In other words FileMaker will do what you want - provided you first tell it what you want it to do.