Trying to build a list of only unique items

[Rockxyzzy]

Hello!

I have a db in which a invoice numbers and PO numbers are tracked. The relationship is such that multiple invoices can be created against a given PO number. I'm trying to use the List function to build a list of unique Invoice Numbers when the user queries for a single PO number. In other words, the user would like to enter a single PO number a see a list of all unique invoice numbers associated with that PO number. I have the search working: it successfully pulls up a list with an invoice number entry for every time each invoice is billed to a given PO, with repetitions.

When I try to use the List function to produce a listing of unique invoice numbers from this found set I'm getting only a single value, not a listing as I would expect. Somehow, I need to make the List function operate over the entire found set and not on a per-record basis.

Does this make sense?

(8.5 Advanced, Mac OX 10.4)

Edited October 13, 2007 by Guest

[Fenton]

First, what do "repetitions" have to do with this? They are not really suitable to use in relational operations, as they only show the 1st repetition; nor are they necessary for this.

This should be a straight relational situation. You say a PO can be attached to multiple Invoices, but a big question you have to answer is: Can an Invoice be attached to more then 1 PO? If so, then you must have a "join" table between them, with IDs from each as foreign keys. If not, then you can put the PO ID into the Invoice record as a foreign key. You should not put the multiple invoice IDs in the PO in repeating field. You could put them into a multi-line field, but that would be awkward.

With the PO ID in each Invoice, you have a straight relationship from PO ID to PO ID.

list with an invoice number entry for every time each invoice is billed to a given PO

Why would a Invoice ID appear more than once for a PO? Is it because you are really talking about Invoice Line Items? That is actually the normal situation; on a purchase order you don't order an "invoice", you order an "item that may or may not be attached to an invoice at this time."

Once the relationships are correct, you can get the Invoice IDs from the Invoice Line Items also. And yeah, there would be duplicates.

The List() function does not take out the duplicates. But the older Value List Items does. So you could create a Value List, filtered by the relationship. Then you could use the Design function ValueListItems (Get(FileName), "value list name"). It would get the unique Invoice IDs. The calculation must have its Storage set to [x] Do not store calculation results.

Or you could use the List() function combined with a Custom Function to remove duplicates.

http://www.briandunning.com/filemaker-custom-functions/list.php

UniqueValues(values)

[Rockxyzzy]

Many invoices can be billed against a single PO during the life of the PO, however, a single invoice never reflects more than one PO.

Yes, you are correct. The invoices are built from invoice items (timecard entries, actually) that are parsed and sorted to build the invoice (obviously, all transparent to the user). And what I'm obtaining is an invoice ID list (with duplicates) as you describe.

I'll take a look at this custom function. Thank you very much! The problem I'm still having, though, is that my List function isn't returning a group of invoice numbers, it's returning a single invoice number entry that's identical to the single record above it. I guess I'm not using the List function properly.

Edited October 13, 2007 by Guest

[Rockxyzzy]

Ok, I've sorta got this working with the Value List/ValueListItems combination. The only remaining issue is that the list that's returned by the ValueListItems contains every invoice number in the system, not just the invoice numbers in the subset of records that I produced by searching for a specific Purchase Order number.

I think the problem is that there's no relationship involved: the invoice number and the po number are in the same table, the timesheet entry table.

You see, this system is for keeping track of a variety of temp employees, placed at various client sites at various bill rates, etc. The PO number support is at the request of a few clients who pay temps against POs that the client has generated internally. So, the user of this datbase is the company that provides the temp employees to clients. The user receives timecard data (which may include a PO number provided by the client) and they enter the employee id, the client id, week ending, hours worked, po number, etc. and the system builds invoices, based upon these timecard entries/invoice line items.

All of this brings me back to the problem: I now have a return-separated list of every invoice number in the system, not just the ones in the subset created by searching for a specific PO number.

I feel like I'm close to having this, but I'm baffled as to how to take it the rest of the way.

Edited October 13, 2007 by Guest