Line item help in [Invoice] file?

[Rangoon]

Just for background, I started with a template from Filemaker called “Music Library” and have now switched to “FM_Starting_Point_2_1” because it offers more of what I’ll need as things progress.  

 

I have a large collection of music Loops in the [Products] file (cuts/Loops from songs in GarageBand).  These are from numerous artists and their popular songs. I have “Product” (for Artist name), “Description” (for Song Title) and “Loop Number”.  The end result is;

“Elvis Presley A Big Hunk of Love 3”

 

Now for my question and it concerns the [invoice] file.

 

In the line items portal of the solution "T12j_invoices_LINE_ITEM||id_invoice|" is set up to enter an ID number (ID Product) and press tab and the description etc is auto filled. 

 

•  I have almost 5000 song loops and growing and I need a way to limit what is shown in the Line Items “ID_Product” drop down list.  If possible by  “Product” (Artist name) “Description” (Song Title) and “ID_Product” (for Loop Number)  like;

“Elvis Presley A Big Hunk of Love 3”.

 

•  If the above is doable, I'll need to change the “ID_Product" that selectes by  ID number to “Product Name” so I can select by;

“Elvis Presley A Big Hunk of Love 3”.

 

Every invoice will have multiple line items with multiple artists like’

Elvis Presley A Big Hunk of Love 3

Police Every breath you take 4

Sugarland Something More 4

Sugarland Something More 7

Rolling Stones Honkey Tonk Woman 5

etc.

 

If the above is not doable or out of the question for a novice like myself.  Just let me know and I’ll assign unique ID_Product numbers and write the numbers down from the Product file and manually enter them into the Invoice file.  

 

Knowing what I’m trying to accomplish if you have any other recommendation I will be happy to hear them.

 

Thank you,

 

HJ

[LVie]

Rangoon,

 

You're right, that is a lot of records in a pulldown menu. If I understand your description correctly you have a couple of options. You can try a filtered value list technique or format the field to use type ahead. I'm a little confused, do you want to display Product-Description-ID_Product in the dropdown or just the ID_Product?

 

Respectfully,

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

[Rangoon]

Hi Levi,

 

I'm sorry for any confusion. I actually need all three "Product", "Description" and “ID_Product” like;

“Elvis Presley A Big Hunk of Love 3”.  

 

Just like the “Elvis Presley A Big Hunk of Love 3” example, I have 16 "Elvis Presley A Big Hunk of Love" Loop/Cuts. So the number at the end of the song is as important as Elvis Presley and Big Hunk of Love.

 

Due to the number of Loop/cuts per song the "format the filed to use type ahead" my not be a good solution to the problem. 

 

I hope that answers your question and thank you for your reply.

 

HJ

[LVie]

Do you mean you need all 3 in the dropdown list as a single concatenated calculation?

 

Levi

[Rangoon]

Hi Levi,

 

Yes that is correct because I don't know any other way of doing it. I have to be able to select "Elvis Presley" and that the song is "A Big Hunk of Love" and that it is Loop/Cut "3".  

 

The only other way I know of doing it is assigning a unique ID to each song in the [Product] file.  That way I could write down each ID from the [Product] file and then enter those numbers into the ID field in the [invoice] file.  That would be a more time consuming way of doing it but it would work.  I would guess that the average invoice would contain 25 line items.

 

I'm happy to listen to any idea you have for me Levi.

 

Thank you for your help,

 

HJ

[LVie]

HJ,

 

I would recommend that you assign a unique ID to each of your song records as a matter of course. There are 2 kinds of ID numbers to consider. The first and most important is an internal auto-entered serial number oftentimes called the primary key. This is the ID that is used internally for relationships and record identification for a variety of other functions. This field should never be seen or accessed by your users. If it gets changed for any reason it will mess up any existing relationships to that record. That's why you want it to stay internal. If you don't already have a field for that I highly recommend that you create one in each of your tables and use that for your primary relationships. If you need help with that, just ask.

 

The second kind of ID is the way you prefer to number your records as a visible identifier like a product number or a stock number. This one can be used as a quick reference for a variety of purposes (like your invoicing). You can create this as an auto-entered serial number or manually create it according to whatever numbering convention you like. If you have this number you can, for example, use that field's list of entries for your drop down list. If you also format it as a type ahead it will shorten the list as you enter the number. Technically you can do the same with the concatenated title you describe above.

 

This includes 2 formatting steps. The first is to create a value list based on the field you want to use for your search filter. Attach that value list to the field as a drop-down list. Then turn on Auto-Complete Using Value List (in the Data tab of the Inspector). When you enter that field you will see the entire list of entries, but as you begin typing the list will filter by the characters you have typed.

 

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie

[Tom R.]

Hi HJ - 

 

You might consider using a popup picker interface. There is a great blog post on it, along with a demo file containing required scripts and so forth, at the FilemakerHacks blog - http://www.filemakerhacks.com/?p=1731. I've used it in one of my solutions and it works great. 

 

Using a drop-down menu with type ahead, you would have to type enough of your concatenated key (Artist - song - loop #) to narrow things down - for example maybe your database includes loops from Elvis Costello songs, you would have to type at least "Elvis P" to get rid of Elvis Costello items, and then "Elvis Presley A B" to narrow it down to just the A Big Hunk of Love items. With the popup picker, a user could type "Elv Hu" (for Elvis Hunk) and it would immediately narrow it down to the appropriate items. Typing "Elv Hu 3" would narrow it down to just the item you're looking for. 

 

It may take a little work to adapt it to your needs, but it would be well worth the effort in my opinion. 

 

Good luck!

 

Tom

[Rangoon]

Hi Tom,

 

I downloaded the demo and plan to give it a good once over after I return from a business trip.  But I was frankly surprised at how well it works, I look forward to checking it out.

 

Thanks a lot for the tip,

 

HJ

[Tom R.]

HJ - 

 

There's a new website called modelarfilemaker.org which is releasing modules with different functionality that are supposed to be easy to bolt into an existing solution. Over the weekend, they released a quickfind module. I haven't played with it yet, but it may be easier to implement in your solution than the first file I mentioned. The particular module is at http://www.modularfilemaker.org/2013/04/auto-quick-find/. 

 

Tom

[Rangoon]

Hi Tom,

 

Thank you for the information.  I went to the website to download Quick-Find and could not for the life of me how to download.  Maybe you can tell me what i'm missing?

 

Thanks again Tom,

 

HJ

[Tom R.]

Hi - 

 

It took me some trying to. Follow the download link on the modularfilemaker webpage, which takes you to github. On the github page, click the AutoQuickFind.fmp12 link. On the resulting page, click the "Raw" button, which will download the file.

 

After playing with it, it looks like the module is for find, only; it doesn't include the record navigation and selection of the first file a mentioned. Maybe Jeremy Bante will include this in a future version (fingers crossed).

[Rangoon]

That did it Tom,

 

Thanks for the help and I'll give it a once over.  

 

Thanks for thinking of me!  

 

HJ