A database design question- getting from A to B..

[squibbler00]

Hello, thank you for your help.  I'm new to FileMaker, after only recently learning Excel, so please bear with me.  I'm working on building a database for my eBay inventory (many different items),  which I will be uploading to eBay's bulk uploading system called File Exchange in .csv format.  I'm pretty far along, but I'm stumped on this issue I'm having...

 

Many of the categories that eBay offers allows sellers to upload ads using a 'standard format' template of feilds that determine the content of the published ad.  However, some categories, such as clothing, CDs, and DVDs, require 'category-specific' templates, which have added fields that examine specific details of said category  (such as size, color, genre, ect.).  Category-specific fields cannot exist in the .csv file if there are any entries in the file that do not suit the category-specific template.  In other words, you have to upload category-specific templates as separate .csv files, you can't just leave the feilds blank if they don't apply.

 

What I have so far is one database with one table, consisting of about 200 listings, all containing the fields of the standard template.  Some of these listings need to somehow be able to reference, or otherwise refer to, the fields that are specific to their category, without having to create a new database to keep them in.  Ideally, from one main file, I'd like to be able to do a search- say for "t-shirts"- and from those found items, be able to (easily) access their 'specific' fields, and export the collection of those fields- the standard and the specific ones- as one file. 

 

I've tried doing this with relationships, but with little experience, I haven't had any luck.  Then I tried figuring out ODBC, and I think that might be right, but trying to find an SLQ server for Macs isn't easy.  I'm considering the FileMaker Server, but thats just a hunch, and I don't know if it's overkill. 

 

I hope this is all somewhat clear, it's difficult for me to explain.  If there's anything that isn't, let me know and I'll try to elaborate.   Thanks again.

[Lee Smith]

First off, what is File exchange?

 

Update your Profile to show your current information on what version you are using, OS and Platform. Here is a quick link for your convenience. MY PROFILE

[squibbler00]

Sorry, I meant to say "FileMaker" in the first sentence, I edited it.  "File Exchange" is a bulk uploading device for eBay sellers, which allows users to publish ads using spreadsheets in .csv format.  eBay provides templates, which use fields to determine the content of the seller's ad- for instance, there are fields for "price," "condition," "description," and so on.  While some fields are required, (i.e. "price") others are not, (i.e. "subtitle").  The database I am trying to build is to keep track of the items that I have in my inventory, and to list them by uploading an exported .csv file of the database's contents.

[eos]

I'm not sure how ODBC or FM Server figures into this …

 

Isn't it a question of 

 

1. Finding records for an item type

 

2. Exporting these records, but only the standard fields plus the fields specific for that type?

 

At a guess, the easiest way (depending on the number of types) would be to manually define different export scripts (or one script with differently defined export steps) which export the correct fields according to type.

 

For a larger number of types, you could cook something up that uses a Virtual table; i.e. find the correct records, copy data from the correct fields according to type (which association would have to be stored somewhere, or encoded in the field names), go to the other table, create a dummy record/row for field names, fill in the data and export.

 

(There's a third approach where you store your field definitions and their type associations as records in tables, and each field for each item is a record in a third table; while IMO this is easier to use in the long run, it's more difficult to convert to from an existing field-based structure, and probably not even necessary. It depends on how else you plan to use this data, besides exporting it and sending it to eBay.)

 

Either way, there's a (little? large?) bit of work involved, and the second approach requires a bit of semi-complicated programming, but this seems perfectly doable. (Famous Last Words.)

 

PS: When you say “listings”, do you mean “records in an Items table”?

[squibbler00]

It seems like you have the right idea.  I know very little about scripts, is that like a macro in excel?  I don't know any programming, so that's a steep climb I think.  Your second approach, the one with the virtual table, is something like what I had in mind.  I built a second database that used the same field names, and tried to link them through a relationship, but that would have been too easy. 

 

PS: When you say “listings”, do you mean “records in an Items table”? - - Yes, thank you for clarifying.

[eos]

[…] I know very little about scripts, is that like a macro in excel? […]

 

Probably; lets just say that you can automate just about any task in FileMaker. See here for the online documentation for FileMaker 13, including an introduction into FileMaker scripting:

 

http://fmhelp.filemaker.com/fmphelp_13/en/html/create_script.13.1.html#1034729

 

I think it would be better for you to try (as a start) the first approach: a script that finds all records of given type, chooses (via a number of If …Else If) the correct one among a number of pre-defined (by you!) Export Records steps, and performs the export.

 

If you have no prior programming experience, that may still seem daunting, but it's far easier than the alternatives. There's the grunt work of manually defining the exports; but you could ease this by starting with a step that has all the right settings plus the default fields, than duplicate and add the type-specific fields. Speaking of which: how many different item types, i.e. export formats are there?  

 

btw: What brought you from Excel to FileMaker?

[squibbler00]

Thank you for the link- it definately seems like I'll benefit from writing a script, if I just sit down with it and try to figure it out. 

 

Even though I've found that some of the functions of Excel are a little more user friendly than FileMaker, I need a more organized, smoother work flow for what I'm doing, and a database seemed like the next logical step.  I'm a business student, and we touched on Access in a class, and being a Mac user, I was directed to FileMaker from online reviews.

 

Thanks again- I'll keep the board in mind, while I attack this little project. 

[squibbler00]

Okay, it's going pretty well, but I ran into a problem.  I've got a script that copies all of my standard fields and pastes them each to their repective fields in another layout, but I'm stuck on how to reference the fields that contain the category specific fields.

 

Right now, I have the specific fields arranged in a different table, and I have that table set with relationships to each of the respective fields in my main table.  I'm trying to look up by "related records" so there is no confusion as to which entry the info is being pulled from.  I'm getting an error for each field that comes from the related table that reads "This operation could not be completed because this layout cannot display the result."

 

Any ideas?  Thanks again for your help so far!

[comment]

I've got a script that copies all of my standard fields and pastes them each to their repective fields in another layout, but I'm stuck on how to reference the fields that contain the category specific fields.

 

Whoa. Slow down. Stop. Back up. Rethink your strategy. How did you ever come to this point?  You should never have to duplicate your data.

 

 

Going back to your original problem, there are exactly two possible solutions:

 

1. Use one table for all items. Let this table have all possibly required fields, for any type of item. Set up the user interface so that the extra fields are available only when needed - for example, you could use a tab control object.

 

Note that when exporting, you can select which fields are exported - and this selection can be remembered by the Export Records script step. And you can have as many Export Records script steps as you need -  either in the same script or in separate scripts - and select among them as required.

 

 

2. Use one table to store all items. Let this table have only the fields that are common to all items. Use additional tables, with one-to-one relationships to the "main" Items table, to hold the category-specific fields for each category. This is more difficult to set up, so I would recommend that at least at this point you choose option #1.

[eos]

Find attached a sample database. It has an Items table with 5 default fields, plus 5 type-specific fields for each of 3 types, and is basically what comment and I both suggested as method #1.

 

I hope studying the script will make this basic approach clearer.

ExportByType_eos.fmp12.zip

[squibbler00]

Thank you so much - the template table you set up is helping a great deal.  I'm still having a little trouble with the script though, but I know it stems from a lack of understanding regarding exports.  Is there some way to export a record/group of records into an existing excel file, rather than as an in individual file?  I tried and got a message pertaining to permissions.

[eos]

 export a record/group of records into an existing excel file, rather than as an in individual file?  I tried and got a message pertaining to permissions.

 

That's the OS's response to your attempt to either save under an invalid path/name, or to overwrite an opened file; if it weren't open, you'd create a new file … with the new contents.

 

There's no built-in “Append to Excel” script step in FileMaker, so this would probably have to be AppleScripted somehow (and the Excel AppleScript API is really ugly …)

 

But before you go down that road: is this for your sake, or eBay's? You know what you have in your database, you don't need to store it again in an Excel sheet. eBay should be able to handle whatever you throw at them.

 

Is your question actually "How can I keep a log of exports, and how do I know which items I've already exported, and how do I make sure I don't export them again?" etc.