Compiling Text in Same Field

[Niet32]

Let's say you have 100 records and a field that stores a city name.

I want to be able to take all the different names and sum them in one field so that they are seperated by a comma, basically creating a string.

i.e Boston, Los Angeles, Phoenix

Is that possible? Any help would be appreciated.

Thx

[LaRetta]

Create a value list based upon values from field and all values. Select your city field. Then create a calculation (unstored) result of text =

Substitute ( ValueListItems ( Get ( FileName) ; "thisNewVLname" ) ; ¶ ; ", " )

[Niet32]

LaRetta...you rock.

OK, one minor (caugh) detail I left out. When I sum the records based on another field, can I have this or a similar calculation only show those cities for those records?

Right now it lists all the cities (which is what I asked).

Seriosly that's a great formulas.

Thanks for any other help.

Patrick

[LaRetta]

Hi Patrick,

I don't know your structure. Is the city in the same table as the calculation? If not (and City resides in a related table) try this:

Change your value list. When it brings up the Specify Fields window (view listing the fields), the top popup should specify your related table (and city field). At the bottom, change the radio from 'All values' to 'Include only Related Values' and select your main table name.

If your cities are within your main file, you will need to determine which field should be used to filter them and the field you want to sum by might just fit (let's call it sumField). So you need to create another table occurrence of your main file (call it Cities). Join Main::sumField = Cities::sumField. Then change your value list as indicated above, ie, use records from Cities and select City. Then below, select 'Only related values' and again select your main table.

This should filter the calculation display to only related cities (within the sumField) for each main record. If it doesn't work for you, we'll need to know more about your relationships. :wink2:

[Niet32]

LaRetta,

Almost there. I am getting the first city but not the remaining cities.

The cities are being pulled in by a related file based on a store number.

[LaRetta]

If the relationship is based upon Store Number then it can only include those cities related to each store. If one store has 3 cities, it should display all 3. What is the field you referred to (and I described) as sumField? In other words, what criteria will determine how the cities should be grouped and filtered? You will need to create a relationship on that field.

It also just hit me ... is this for a sub-summary report? It might help to post your file. :wink2:

[Niet32]

This is for a sub-summary report. I'm always forgetting those tiny details.

[LaRetta]

Another small detail forgotten ... my question about which field determines the group of cities.

I need a clearer picture. You have a sub-summary report. You have a leading part which is based upon which field? You want the cities (matching this leading part field) to appear in the leading part, ie, signifying that this group of records (based upon which field) all belong to the cities in this calculation.

So far, I have no idea how to group the cities. You haven't attached your file nor indicated that you even read that request. Help me out here... I won't do it all myself because I simply can't. Give me picture of what you are trying to accomplish. Answer my questions. Okay? I'm not a Dentist.

LaRetta :wink2:

[Niet32]

LaRetta,

I just got a chance to get back on here and thank you! I did figure this out like you said. My Dbase is referring to several othre dbases and I had just not chosen the correct one in the Value List screen.

I then tried the formulas in another Dbase st up almost exactly the same. Back to the drawing board. I'll let you know what I find there.

Thanks again!

[alimrb]

As far as what Filemaker is- it's a database creation program, that also lets you create the forms for data entry and reports. Used to be flat-file only, but now has some relational db features, I believe. You might compare it - sort of - to Access, but it's simpler and less programmable than Access IMO. Filemaker has a long Mac history, and a reputation as THE database creation program for people who are afraid of databases. Take from that what you will.

As far as migrating from Filemaker to a web app- unless you are going to just put the existing Filemaker db on the web - Filemaker is a database CREATION app. So there is nothing AFAIK that is a drop-in replacement for Filemaker for the web. You would have to create a database to replace the Filemaker database, then write/program a website that accesses that database.

I hope that clarifies things.

I might also add that Filemaker is great for small departments, but will eventually fail you if your company is growing quickly. This is also true of Access, though I believe Access has somewhat higher limits. You should be making frequent backups of all your databases, whether Access or Filemaker- VERY IMPORTANT!

You may want to think at some point about moving ALL your databases to a "real" server database like SQL Server, MySQL, etc., or even Oracle if you have tons of money(yeah right!). An interim step could be Microsoft's free SQL Server Express, aka MSDE. You will hit limits with it as well, but not as quickly, and it's a pretty simple and painless upgrade from it to the full version of SQL Server. You should realize that this will involve a complete re-write of your database (though you can import your existing data if you are careful with your db design). This is not necessarily a bad thing- many, though not all, Filemaker and Access databases are put together quickly to serve an immediate need, by people with little or no database design knowledge/experience. They may be fine when the department is small. Unfortunately, many of these dbs are then outgrown when the company gets bigger, and the design choices that were good enough once are no longer working. Databases like Filemaker and Access are file-based, rather than server-based, and are not designed from the ground up for access over a network or the Internet; they are not as capable when serving multiple users, and are not as robust in these situations. These issues can lead to data corruption, not filling the business' needs any more, etc., and a lot of these apps become mission-critical. Re-writing these apps in a more capable system can have many benefits, such as speed, more capabilities, better and faster backup/restore cycles, etc. - server-based databases are generally designed to be fault-tolerant, are easier to recover from problems and recover more thoroughly, etc. Have you ever had a user's PC crash when he was logged into an Access db over the network? I have and it's not pretty...if you have VERY recent backups, you can recover most of your data in a crash situation, but with a properly-set up server db, you can recover nearly ALL the data...

Keep an eye out; the time may come to move to a bigger system...

[alimrb]

I am not by any means a FileMaker expert, so I will defer to your expertise on FileMaker Server Advanced.

"100 simultaneous web-based users" - this only supports my original statement that FileMaker will eventually limit you in a fast-growing company. A quick glance at the FileMaker Pro website shows a definite focus on small business. When I follow the links for "Medium to Large businesses", I see statements like "30,000 recordings", "10,000 design jobs" in the solutions mentioned. To me, these are not large databases. I just completed an app using Access and ASP.NET that imports files containing nearly a million records on a monthly basis, and supports lookups of tens of millions of records, in seconds. I have heard of SQL Server databases containing data in the Terabyte range.

There is no way I will believe that FileMaker Server is as robust as SQL Server as far as data retrieval in the event of a server crash, etc. SQL Server has dozens of features that allow reconstruction of the data up to the moment of the crash, as well as supporting mirroring of data across servers in different parts of the world via the Internet, server farms, log shipping, etc.

I note that conspicuously absent from your list of Technologies that are supported by Filemaker are the Microsoft technologies like OLE DB, ASP, ASP.NET, etc. This may have been an oversight, though a quick glance at their website suggests not; but, like it or not, the fact is that a large percentage of new web development is being done with MS technologies. Yes, you can use ODBC (which FileMaker supports) from MS technologies, but it is much slower and not as type-safe when used from ASP.NET.

There are many other db choices that work equally well with MS Technologies, such as the open source db MySQL, and the very-expensive Oracle.

I also found that Filemaker's development environment was more than a little bit quirky, and unlike other db products. I am sure that this is a large reason for Filemaker's appeal among its users- it suits their level of knowledge and makes them comfortable, as beginners; and as some of them become more accomplished as developers, they are used to the environment, and so they stick with it.

I will go out on a limb here and suggest that, IMO, FileMaker's scripting is inferior to Access' VBA programming environment; and Access and SQL Server can both also be programmed by full-fledged programming languages including C++, Visual Basic 6.0, Visual Basic.NET, C#, Fox Pro, etc. - in fact any language that supports COM objects.

Not to mention that you can use ASP.NET, in either VB.NET, C#, or Managed C++, to program Access or SQL Server for the web, right in the same full IDE you use to write any other program. You have full debugging support, with variable value inspection, break points, step-by-step advancement through the code, etc.

Perhaps Filemaker 8.0 is now greatly improved, but the company still needs to move past the product's image as a Mac-centric, small-department, suited-to-the-database-novice app.

I have had to completely re-write several Filemaker apps in other products like Access, SQL Server, etc., and often found that the Filemaker db was built with no attention to database basics like proper relational design, table normalization, etc. Undoubtedly this has more to do with the limitations of the users who created the databases- I have dealt with similar problems in Access dbs- but I feel that, in my experience, FileMaker attracts just the type of user who has no database creation knowledge, and Filemaker makes it too easy to create these types of "problem databases". I see many many cases where FileMaker - yes, and Access too - were used to throw together a "quick mailing list" or other database, which eventually was extended by other people, none of whom had real db development experience or knowledge, until it became a mission-critical application; at which point it began teetering under the weight of uses it was not properly designed to support. Again, this is not unique to FileMaker, but its ease of use for basic database creation makes this situation more common. Perhaps the newer versions have changed all that- the last version I had my hands on was 4.1 - so I will reserve judgement.

I would also like to point out our relative biases here - your shop is entirely based on FileMaker, while I am mainly Microsoft, doing my web dev work in a combination of HTML, CSS, ASP.NET, Access and SQL Server. So of course we are going to support the technology we are comfortable with and know best.