Trouble doing either lookups and/or setfield

[revjj]

I'm using FMP 11 and FMP 12 and am having a terrible time setting up lookup and even set field functions.  Can anybody send me some relationship example pics, some field definitions pics and some scripts or anything else that would help.  I've looked up umteen tutorials online and I cannot get anything to work.  I thought it was a problem with my software (FMP11) so I upgraded to FMP12 and still no luck.  I'm sure it has to be something simple that I'm missing.  I have done this before but that was years ago.  A functional file for FMP 11 or 12 would be great.  All help is appreciated.

[comment]

I suggest you ask a more specific question. What is the actual problem you are trying to solve?

Note that lookup and Set Field are two very different things. Lookup is an auto-entry option for a field. Set Field is a script step. A script needs to be called, while a lookup happens automatically during data entry.

 

[comment]

FWIW, you can find a demo file showing a classic example of looking up a price of a product into an invoice line item here:
https://fmforums.com/topic/63425-auto-fill-one-field-with-text-from-two-fields/?do=findComment&comment=300150&_rid=72594

 

[revjj]

Perhaps this pictorial will clear up what I'm trying to achieve.  My original layout for data entry looks like this:

My next layout looks like this:

Those are pretty straight forward.  These layouts associate as such:

Pretty much straight forward stuff.  I want my next layout to look like this in table form:

Lets say that items "A" are books, items "B" are markers and items "C" are erasers and so on.  The association would look something like this:

This is why I was looking to use the lookup or setfield function to move the individual fields around from a horizontal layout to a vertical layout separated from their individual record sets.  No luck getting that to work, not even the function to work in a seperate test file.  I must be missing something real simple.  I thought maybe using containers but have yet to struggle with that.  I just want to move data from one bucket to another.  The  move would have to incorporate DATE, ITEM TYPE and DOLLAR AMOUNT.

I hope this clairfies things.  Any thoughts?

[comment]

20 minutes ago, revjj said:

move the individual fields around from a horizontal layout to a vertical layout

To begin with, this has nothing to do with lookup or Set Field. But before I continue, I need to understand (a) what do you mean by "separated from their individual record sets" and (b) why in your mockup output the 5th record has no entry, while the 4th one has entries in two columns.

I also don't see that "[the]  move would have to incorporate DATE, ITEM TYPE and DOLLAR AMOUNT". I only see the amount being moved to a column corresponding to the type.

You should also know that there is no way in Filemaker to have a dynamic number of columns. Filemaker will add as many rows as you have records, but the number of columns must be fixed in advance. The most you can do is hide unused columns. Filemaker's "native" method for a report by type is to show a sub-summary row for each type, not column.

 

[bcooney]

Looks to me that your goal is a pivot table. Not something native to FileMaker. As comment suggests, you can report vertically by category using a sub summary report. 

[revjj]

To clarify (a), I guess I want to separate some fields from individual records and display them in column format with associated date and dollar amount for presentation purposes only.  Regarding (b), I got a bit in a hurry finishing the mock-up.  It should look like this:

So, from what I hear you saying is that I cannot set up a new layout with perhaps a new table with a single row array with various "buckets" to move data from the existing records and then create another record for the row of "buckets" for other related data associated to the date of entry.  I'm not a pro but, as flexible as I see FM is, I can't believe that this solution is not posssible.  Not that I don't believe your feedback.

 

When I say "separate some fields", I mean create a field or "bucket" in another table containing the data from any other field of choice.

[comment]

I still don't understand what you mean by "separate some fields". And I don't understand the TRASH column.

Other than that, the display you show is certainly possible (with the limitation I stated earlier regarding the number of columns being fixed in advance). It does NOT require using another table; in fact, that would be much more difficult to implement and I cannot see what advantage it would bring. All that you need is a single calculation field in the existing table using a formula like: 

Let ([
types = ValueListItems ( "" ; "Types" ) ;
label = GetValue ( types ; Get (CalculationRepetitionNumber) )
];
If ( Extend ( Type ) = label ; Extend ( Amount ) )
)

where "Types" is the name of the value list you use for the drop-down. Set the result type to Number and the number of repetitions to the number of the values in your value list.

Place this field on the layout and set it to show the repetitions horizontally.

 

[revjj]

Trash is just another column header.  Like an entry point to catch how much I spent on the trash being taken away.  Trash could have been "RUBBER BANDS".  When I say "separate some fields", I mean possibly create a field or "bucket" in another table containing the data from any other field of choice.  I'm not sure how else to explain it.  Maybe grab the data from a field from say, "DATE" in a record and put it in a "bucket" or "placeholder" in a table to be displayed in a vertical manner in a different layout, in table form and just use a header to identify the data in the column where I place the field, be it books, markers, erasers or trash.  I've never used the LET calculation.  I suppose I could putz with it and see how it goes.  Thanks again.

[comment]

6 hours ago, revjj said:

Trash is just another column header. 

But in your example there is an entry in the TRASH column in record #3 while the record's type is Item "C" which you designated as "ERASERS" - and the full amount does indeed appear in the ERASERS column alongside the unexplained entry under TRASH. That is confusing.

In any case, I suggest you try my suggestion and tell us how - if at all - the result differs from what you expect.

 

[revjj]

Wow, that "LET" approach is a bit more complicated than I anticipated.

[revjj]

Hello again all -

I have got my "formatting" problem resolved.  All looks good.  I'm now looking for a way to automatically inject a mouse click or enter key once I enter a different layout.  I have one layout that is used to enter data.  I have another layout that sums the data.  When I enter the summary layout the summed data does calculate until I hit the enter key, tab key or click the mouse.  This summary layout is for viewing the data only.  I'd rather it just calculate the results automatically and display it without intervention.  Any thoughts?

 

Thank you in advance

[revjj]

Hey folks -

 

Never mind.  I got it figured out.  It's the "Commit record/request" function.  I'm a bit rusty but I'm getting back into the swing of things.

[comment]

Ah. Now I understand your question. You meant the summary fields do NOT calculate until you hit enter key.

[LaRetta]

On 1/11/2022 at 2:20 PM, revjj said:

Hello again all -

... the summed data does calculate until I hit the enter key, tab key or click the mouse. 

Yes, I didn't understand the issue either.   All I could think was there must be an OnObjectExit script trigger on the field but that didn't make sense.  I'm please it is resolved. 🙂

Edited January 13, 2022 by LaRetta