Thank you for your post.
I don't know how you have your tables set up. It sounds like you have the Invoice number assigned to each item in the invoice. You may want to consider the following:
<other client info>
<other Part information>
<other info like quantity, extended price, total invoice, etc.>
You would have your Client table connected to your Invoice table through Client ID, and you would have your Part table connected to your Invoice table through Part ID.
In your Client table, you can create a portal into Invoice table and just display the Invoice Number. This should grab each individual Invoice Number for that Client ID and display in the portal.
To create a portal, pull down the View menu and select "Layout Mode".
On the left side of the screen, click on the icon just below the oval tool. This is the portal tool. You click on the layout and draw a box. Let go, and it will ask you for the related table (select the Invoice table), and then the fields (select the Invoice Number). Return to Browse mode, and you will see all the Invoice Numbers for each Client.
If you run into any difficulty, or need clarification, please let me know.
Thanks for your quick response.
When I do "normal" invoicing systems, I can do that, but this one is very unique and complex. The invoicing is done through QuickBooks and QuickBooks assigns the invoice # to each line item as it transfers to QuickBooks. I cannot use a traditional invoice table because of this.
I do run across this problem more than in this application also. I would love to be able to create alist from a data set that shows me only the unique #s or text. I have used other DBs that allow for list filters for unique values.
Any other ideas on how to accomplish this?
Thank you for the clarification.
It sounds like you can still create a separate Invoice table that keeps track of the Invoice ID. Once you import all the data from QuickBooks, I would then import the Invoice Number and Client ID into a separate table. Then, remove the duplicates via a script so that there is one instance of the Invoice # in that table. You can then still use the example I used previously.
Hi davenfla / TSGal
If you really need to filter a list of non-unique values to be unique there are two ways to go about it. The first is a recursive Custom Function, and the second is using a Value LIst.
First off a Custom Function that uses recursion to filter you list. Here is a custom function I have used in the past to do something similar to you.
xCF_UniqueListFilter ( listToFilter ) =
Let( ActiveValue = GetValue ( listToFilter ; 1 )
; // Start of calculation
IsEmpty ( listToFilter ) ; "" ;
If ( ValueCount ( FilterValues ( listToFilter ; ActiveValue ) ) > 1 ; "" ; ActiveValue & "¶" ) &
xCF_UniqueListFilter ( RightValues ( listToFilter ; ValueCount ( listToFilter ) - 1 ) )
) ) // End of Let function
If you have never used custom functions before, you can setup the custom function by going:
And the function will display near the bottom of the Calculation Function list, or you can view "Custom Functions"
- 'File > Manage > Custom Functions...'
- Click 'New'
- Then copy and paste the above calculation into the appropriate fields
- Function Name: = xCF_UniqueListFilter
- Function Parameter: = listToFilter, click the green plus sign
- Then the rest in the calculation box.
- Click 'OK'
- Create a new calculation field in your CLIENT tabel
InvoiceIDs ( Calculation ; text ) =
xCF_UniqueListFilter ( List ( ORDER ITEMS::InvoceNumber )
And you will see the list of unique Invoice ID's you can sort the list by sorting the relationship between the TO's on the relationship graph.
The second option, uses a Value List to filter the items for you, hand if you want to have them sorted also.
- Setup a new Value List, 'File > Manage > Value Lists...'
- Name it "InvoiceIDList"
- Check 'Use values from field:' and click Specify field...'
- Specify the ORDER ITEMS table and select you Invoce Number Field.
- Now make sure to also select 'Include only related values starting from:' to be you main CLIENT table.
- Now OK that and the Value List is setup.
- Next you need to create a Calculation in you CLIENT table:
InvoiceIDs ( Calculation ; text ) =
ValueListItems ( Get ( FileName ) ; "InvoiceIDList" )
And this should then display a unique list of invoice numbers. I have used this method a few times but not for this scenario so would be interested to hear if it works for you.
I hope these options help and do ask if you need any help with any of these.
Orlando - Awesome! That's exactly what I was looking for! I'll give it a try and update the forum.
Worked awesome! Thanks again
Glad I could help.
Which option did you go for, Custom Function or Value List?
The custom function did exactly what I needed. I have been working with FM since 91 but always stayed away from using custom functions. Now that I see how powerful it is, I'm going to use it more. Do you know of any good resources to learn more?
Good to hear.
I was the same when Custom Functions were introduced, but once you get started they become very very useful. Recursion took me a very long time to get my had around.
The number one resource is Brian Dunning's brilliant site
Also an demo file and artical that realy helped with recursion was by Excelisys
http://www.excelisys.com/web/downloads/index.php at the bottom of the page.
Finally there is a great tool for storing and testing your Custom Functions by Matt Haughton of First Contact Solutions called CFExplorer, a must have developer tool.
Good luck with your venture into the world of Custom Function and post if you need any help.
"The number one resource is Brian Dunning's brilliant site"
as uLearnIT also knows, there will be another site for custom functions...
Now is on Alpha testing, but I'll come back to say when it will start !
Don't leave us hanging like that, give us some more info on this new site. When can others test it?