Pool of product IDs - filter by used or unused

[jn08]

Hi,

I've got a pool of product IDs (it's for a book database - so these are 13-digit ISBNs). I get these from a pool of IDs that I keep in one table. I'd like to allow users to select an ISBN from a drop down list and assign it to a product in another table, without using an ISBN that has already been used before.

I've managed to use a value list to get the drop down box happening, but can't seem to filter it based on whether it already appears in the product database. I tried using a calculation field in the ISBN pool table that verifies whether the ISBN is used in the product database (I set the relationship to link the ISBN field). The field will give me a boolean response which is correct, and I can even use conditional formatting to strike out the ISBNs in the pool that have already been used (this makes it easy for me to see which ones have already been used when I import more ISBNs later down the track). However, I can't seem to use this information to filter the value list.

Does anyone have any tips on how to do this? Or if I'm doing it completely wrong, any starting points on how to do it differently?

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/274656/

[jn08]

I'm not sure if that's exactly what I'm after ... I'm finding it a bit hard to follow. I need to keep an historical record of which ISBNs have been used and which haven't. Would this method allow for that?

I've tried wiring up an example, but it doesn't appear to be working. I suspect because I have no idea how this is supposed to work, I just tried to follow along. Any help would be appreciated.

http://dl.dropbox.com/u/290845/Book%20Database%20ISBN%20Dwindling.fp7.zip

[comment]

Does it matter which ISBN they pick? Couldn't they just automatically get the first unassigned ISBN?

[jn08]

Absolutely, yes. So long as it hasn't been used before, it doesn't matter which one. Also, I need to have the ability to assign multiple ISBNs to one product, as sometimes we use multiple ISBNs depending on how many formats we output. Any suggestions on how to do this?

[jn08]

Perhaps it would be easiest to do this with a script instead? Perhaps a script that determines whether an ISBN has already been used then assigns a fresh one to the field (and marks the existing one as used?). Any pointers on how to start with such a script?

[comment]

I have modified the technique to remove ALL used values, regardless of which parent record has used them.

OverallDwindlingVL.zip

[jn08]

Great! I've got it working now as hoped. Works pretty well. I'm not entirely certain *how* it works. (What's the global field "-" for? And what's the calculation with the carriage return in the cUsedISBNs actually do?).

But those questions are secondary. The next step is to refer to the various format ISBNs in exports and other layouts. For example, I have a metadata export layout that includes all the fields that need to be sent out, all in a particular order. Is there a way to refer to a specific format's ISBN in separate fields? I need to do some post processing on the ISBNs (aggregation) and send them separately.

I couldn't figure out a way to do it, so my workaround is removing the format field from the ISBN join table (affiliations) entirely. I'm now allocating ISBNs using the join table, and then assigning allocated ISBNs to a format within the product. This works well until you need to delete an ISBN, because then it has to be deleted in two places. Is there a way to refer to different related fields (by criteria) on the same layout without using a portal?

[comment]

What's the global field "-" for?

For visual separation only.

And what's the calculation with the carriage return in the cUsedISBNs actually do?

As its name implies, It list all the ISBNs that have been used.

I am afraid I didn't understand your next question.

[jn08]

What I'm trying to do is export all the relevant fields from my product table along with multiple related fields in a metadata export in one spreadsheet. I have all the product information in one table (except, now, for contributors and ISBNs). I need to send all this information out in a flat CSV or Excel format. What I've done so far is set up a layout with all the fields in a list. I "export records" and all the fields display in the right order as column headers in the Excel spreadsheet.

What I'd like to be able to do is display specific related fields in the same layout which will export on the same spreadsheet. But I presume with a portal that won't work, right?

At the moment I've got the author name displayed in the export data by using an "Author name" field in the contributions table. This name is pulled from the related contact using a "case" calculation which I think you suggested. "Case (Role = "Author" ; Contacts::Author Name)".

I'm not sure how this will work when I have multiple related fields to export, though (an author and an illustrator, for example). So far when I've tried the same technique using the ISBNs (trying to display print format and ebook format ISBNs, for example) it just won't display both records on my layout. The first one will display (the print ISBN) but the related records related to the ebook ISBN won't display.

Does that make sense?

[comment]

The answer is the same as the one here:

http://fmforums.com/forum/topic/80953-function-to-determine-whether-a-data-from-multiple-related-fields-contains-a-particular-value/page__view__findpost__p__376284

You must decide what your exported flat file's columns will be, and what will each row represent. Keep in mind that not everything can be easily described by a single flat file - for example, a book that has both multiple contributors and multiple formats.

In any case, the structure of your solution should not be affected by this (unless building the exported file is its sole purpose).

[jn08]

I've already got the export file set up, and I don't have control over column headers, it's a set format. It has columns for multiple authors and multiple ISBNs. However, if I set it up that way in the database it wouldn't be very useful for anything else, so I want to keep the relational set up.

I've figured out a solution for the multiple ISBNs. I have added a calculation field in the product table with a List() calc that lists a concatenated format of the Format & ISBN. I then wrote a text parsing calculation for each specific-format ISBN field to ensure they all have the right ISBNs split out to the right fields for export. It was easy enough to do because every ISBN is 13 digits long. Will be more difficult for the multiple contributors situation, but I reckon I can still probably do it. Fiddly, but once it's set up it should just work.

[comment]

It has columns for multiple authors and multiple ISBNs.

I am not sure what exactly that means. You cannot have multiple columns for multiple authors, unless you define in advance that each book has exactly 5 (or any other fixed number) authors. Then a book with 3 authors will have 2 empty cells, and a book with 6 authors will be one author short.

OTOH, If your target application can receive multiple authors (or any other "multiple" data) as a list within a single cell, then this should be quite simple (probably simpler that you describe).

[jn08]

The target spreadsheet has fields (columns) for four authors (or perhaps it's five, I'd have to check). Most of the time there's only one author, so most of these fields are blank. So the extra fields need to remain blank if there's only one author (or two, or three).

I've figured out (sort of) the author situation. But I feel like there should be an easier way. I use a similar method to the multiple ISBN allocation described above. However, the text parsing is a lot more complicated, as author name fields can include multiple names. In the end I managed to write a calculation that allocated the Author to the Author export field, and the Author 2. I can do the same for further authors, but haven't yet.

It would be far more simple if there were a straightforward way to include multiple specific related fields on a layout to be used for export. Or if portal fields exported like normal fields.

[comment]

The thing with exporting to a flat table is that at some point the data needs to be flattened. If you want to export directly to CSV format, then the flattening must be done inside Filemaker - meaning you'll need a Filemaker field for every CVS column to be exported.

This is certainly possible, but it's also cluttering your solution with resources that would not be required otherwise. In the current example, you would need to add 4 calculation fields to calculate (up to) 4 author names - and either a filtering relationship or a custom function to separate the authors from other types of contributors.

My preferred approach is to export as XML and have a custom XSLT stylesheet do the flattening. Another option is to export as Excel, then run a macro to flatten the resulting sheet. These two are the only non-flat export formats - other than .fp7, of course.