Neil Scrivener

That's true and makes a lot of sense. Thanks!

Thank you. As always the advice is spot on. I need to learn scripting better! It was the fact that I didn't have the "" and the &.... so it worked in my mind. Also, correct on Global Field. I created the Field as a Global Field, for the purpose of searching only. Thanks as always

Hi Guys, I have a Script running a calculation for a Find. The table is in essence as follows Item Percentage Find Field My Script/Calculation is as follows: I have an identical Script with text relating to the item. This works perfectly. For some reason the calculated field isn't. The input for this Find Field is entered as a decimal number (the setting of the the field turn it into a percentage). So if I am looking to find ≥ 10% I would enter .1 into the Find Field. When running the script, I am getting blank, with no matching records - despite the fact I know there are records higher than 10%. Am I doing something obvious wrong? I've also tried Find Field ≥ Percentage, but getting the same result - with no records matched. Thanks! N

Got it! Thanks. For anyone else reading who experiences the same issues - I believe you need to: 1. Perform the Find in your Criteria 2. Sort Records I'm going to make a script that does this, and then when clicked takes you to the Print option - which should package everything nicely up

UPDATE, ignore my first issue. It was because it was a percentage I was filering, so I needed to put in .18 rather than 18. Second issue I remain stuck with

Thanks for this, I've ran into two problems: The first is that when using Find, inserting >18 I'm getting a message saying "No records match this criteria". However >0 works perfectly. Why is this? The second is that I have a nicely laid out report when sorted by Type. I have a sub-summary with with Type heading, and then the Body contains the vegitables and quantities. When performing a Find (as per the working one, above), my lovely titles and headings are vanishing, and I'm getting hard and fast data. I guess this is because Find it showing 'Body' only - but when creating a Report (say a paper of PDF), I'd want to perform the Find, and then keep the nicities of my Header, Sub-Summary, Footer etc.... How would I do that? Thanks! N

Thanks for this will give a go. Should I be creating a Script to run the Find, so that if additional value are created - or that the Find gets reset, it can be re-instated? Or is it the case that once Find has been activated, it's 'locked in'?

Hi Guys, I'm back, with some final (hopefully) questions on Reporting. Assuming the Data looks like this: I can create Reports from the Item, Type, etc that's easy. What I'm struggling with is the following: 1. Making a Report which is sorted by Type, but only where the Qty is above 0. So where Bread, Cheese and Crackers would be excluded. 2. Making a Report which shows the 'top 10' owned, and 'bottom 5' owned - based on qty field. 3. Making a Report which shows me the 'best' month (a) per product and [which month did I have the most of all items] and (b) the same in year. I'd appreciate any help or advice Thanks! N

Unfortunately no cigar: This seems to be exactly as per your directions, unless I've done a typo. 😭

Thanks, I should make it clear that this is a completely new territory for me. I really don't know anything about anything I'm writing here other than what you're telling me. So I am taking everything extremely literally while I learn this syntax. I simply don't understand the SQL side, I'm happy to learn, but it's a slow process. Turning to the calculations, I've made the following changes: and I'm getting Table Missing. Should I delete the "GROUP BY" command?

Makes sense thank you. So with the script working, I guess I need to do a lot of learning on Scripting. So I've taken this Script added as a Custom Function. Easy. I've inserted it as a Field. Easy. But I'm getting the result below. Is there a straightforward way I can manipulate this to show me just the Number that is related to record only. For example in the first record Apple is the 'Type', so I'd like Items in April to show just 16. Also, if I change the date, I've noticed the quantities don't update. Do I need to add a Refresh command to refresh the calculation when a Date or Quantity is changed? ]

Thanks it's working, oddly it was the | in the calculation that was killing it? But I notice in your original you had it, and it worked?

The plot thickens. I've opened up your file, and it's not sent over the Data Viewer info. Added the following and getting "Table Missing"

Thanks for uploading this. I've added this to my Database, but it's not working. Have you Shortcuts which it is triggering through some of the above?

Great, thanks. One final question on this. Is there a temporary fix I can use in the way of a calculation to resolve this particular issue? Totally understand the arguments on speed and efficiency - but also mindful that I want this database to 'work', and would rather have a beta with a slower calculation whilst I learn the BI and Scripting - than delay getting a beta version together whilst I learn something else, if that makes sense? Thanks! N

This is very useful thank you. Is there a good resource place to learn about the BI side?

What I'm in essence trying to do is work out a snapshot of how much the items were worth per month. Which will tell me if it has lost or made money. So the eventual calculation will tell me: - How many units did I own in that month? - What was the total cost of those units in that particular month? - What is the value of those units in a month according to the particular month in the Valuation portal? - What for that month is the outcome, both £ and % wise. Hopefully that explains better.

Thanks for the info guys. If i was comparing for the month of April, would I not need to have a 'start' date' too? I've put a quick demo file of what I'm looking to achieve. So if you look on the Companies Layout, I'm looking at the Owned field in the Valuation Portal to be be a calculation at that snapshot in time. So where a Valuation is on 19 May 2020, I want it to tell me at that point how many items I 'own' based on the Records under the 'Previous Data of Transactions' field. If that makes sense? Demo File+.fmp12

Hi Guys, So I am CLOSE to my near final question, until it comes to reports.....yay! This is possibly opening a can of worms, but I want to create a Date Calculation. The table will look something as follows: Type Buy/Sell Date Banana 7 10 April 2020 Apple 10 5 April 2020 Apple -3 9 April 2020 Bananna 4 17 April 2020 Apple 9 14 April 2020 Bananna -4 20 April 2020 Apple 16 30 April 2020 I want to run a calculation to say "How many Apples did I have on Date X?". The date will come a TimeStamp on another table. The correct answer is of course 32. The same formula for bananas would be 7. So really my questions is, what would I need to go about making a calculation like this? Once this is mastered, I think/hope to have no more questions other than on Reporting! Thanks! Neil

Makes perfect sense, thank you Nx

Sorry, So at the moment we are using Last() based on the sorted records. Is there a calculation I could use along the lines of Last + Date, so if the Records were accidentally sorted, it will still find the Last be referencing the date as the 'most recent' date?

Thank you for this. Surprisingly I have put it together and seems to be working well! To this more precise, and to avoid any sorting issues, can I change the Last() to add a Time and Date, so it gets the 'true last - how would I amend that calculation to include a Time/Date? Thanks! N

Hi All, So my ambitious database is coming on well, and in particular thanks to Comment in this forum! I'm on to the final chapter of impossibilities and the current one is below, which I don't really know how to start. So I have a table of information, which has what I would call an 'original' piece of data, a number. Each month, that number gets updated, on a monthly basis, at the end of each month - the idea being that you can see things in a shapshot of the present, and in the past if required. So when you add Record in the Numbers Update Table and select the Company, it replaces the 'old number' with the new number, and on it goes. There would be a Calculation which takes the 'new number' to work out the difference from the original number. In the ordinary course of things, where there are multiple fields in the same table, I'd use a Get List Values calculation - but because the 'new' number is being added each time a Record is created - I'm a bit stumped how to go about this. To give an example of what I'm looking for: Numbers Update Table Company Month Number Company A March 12 Company A April 3 Company A May 5 Companies Table Company Original Number Particular Month Number Difference Calc Company A 100 as per reference from Record Company B 200 as per reference from Record So in essence, the Difference calc would show, for Company A in March, 103 in April and so forth. How would I go about this? Thanks! N

Thank you for this, adjusted in my Demo Database and all working good. So, having performed the equivalent calculation in my actual database, I am getting the following information: Company A Percentage Difference - Year 1 25.81% Percentage Difference - Year 1 21.37% Percentage Difference - Year 1 17.45% Percentage Difference - Year 1 4.49% Average Difference: 2,616,075.00% So this seems to be taking an average of the entire table of data, and not 'filtering' it by the Company record that I'm in. Any views what could be wrong? Thanks! UPDATE I've added the field to Transaction Data layout and getting a figure of 23,215,055.28%! Now I'm really puzzled?! UPDATE to the UPDATE, Ive changed the calculation to a "Total Of" instead of Average of, and it's giving me 69.11% - which is the above percentages added......so something is working! Update to the update to the update: have now switched it back to Average Of, and by some magic it's working. Possibly I bug, not sure?!

Haha, the wonders of the internet. I've knocked up the attached Demo File. In my actual DB, the Portal in Companies lists the transactions - not sure why it's not working in this? But you can see in the Companies Layout (hopefully) what I'm trying to get, in the Average Increase/Decrease Field? Thanks! N Demo File.fmp12