How to find populated portals and export the data to tab?

[dkey]

Hello I have two related db which returns a proper result in a portal I created

As the records involved are many how to write a script to only find the populated records and then export them to a tab file for additional work?

Thanks and Regards

Dan

[LaRetta]

Hi Dan, 

We can assume the portal is the 'many' side to the single parent record.  If you want to export the child records, you must go a layout based upon the child table and export from there.  I am unsure what you mean by 'find the populated records' ... can you explain more?  If you mean, only those in the portal then you would (pseudo-code):

Go To Related Records [ layout based upon child table ; show only related matching on current record ]
Export Records (select TAB).
When specifying fields, you can also select your parent table's fields.

If your portal is filtered, you will need to first go to the portal and THEN issue the GTRR.

Can you explain a bit more of precisely what you want?  Only records in the portal for the current record or the parent found set instead?  Is the portal filtered?  If you export from the child table and select parent fields, you will end up with the parent fields on each line of the child fields.

[dkey]

Thanks for your reply I will explain better

The first db Has a number of Video files: I only have 2 fields Clip (text) and Record date (timestamp)

The second DB has a number of Audio files: 4 fields Sound (Text) Record date, START and END (these are time stamps)

The relationship is as follows

Audio TABLE where AUDIO START and AUDIO END relate to the Record Date of the video file.

I can get a result telling me if within a certain amount of time I have both audio and video files recorded within that period of time.

The portal tells me which Video files are within that range based on the Record date of the audio

I include a short example of my work

The Video Calc file is just a reference for the audio file

I have a script there "Empty Script" but looking at the results I am sure I could do better than this

 

Thanks and regards from a really old user of File Maker which I bought when it was a company of its own long before Apple bought it --- but I am always learning and at my super old age I experiment very little

AudioCalc.fmp12

VIDEOCalc.fmp12

[LaRetta]

If I understand your need, you wish to find all Audio records where they have ANY video children?  If so, you need to search any field in Video which is never empty.  It is always best to include a unique ID in EVERY table.  It can be a simple auto-enter serial number or a calculation such as Get ( UUID).  Ummm, are you still on version 13?  If so, forget the UUID and just include a serial number.

But you CAN use that Video timestamp field if it's never empty or the Video field.

A script would then look like (generated from Audio):

Enter Find Mode [ pause off ]
Set Field [ Video::record date ; "*" ]
Set Error Capture [ on ]
Perform Find[]
If [ not Get ( FoundCount ) ]
... do whatever you need to because no Audio records were found which had videos ...
End If

The '*" when used in a search in any field (except Container or Summary) is telling FileMaker ... find records with NON-EMPTY values in that field.  Is this what you need?

If this is what you want, then you can continue the script by then taking your Audio record set you just found, Go To Related Record as I've indicated, and export the Video children.

But really, even if you DIDN'T perform a find first, if you show all Audio and complete the GTRR (using MATCHING ALL RECORDS), you would STILL only end up with Videos which were' found' so I doubt you want to export the Videos, is this right?

[LaRetta]

Hey Dan, I wanted to clarify one additional point ... you probably were aware but if not:

In FileMaker, perspective is EVERYTHING.  So, from the perspective of Audio, if you look to your Video table occurrence, it will take into account its relationship.   So searching Video (table occurrence) from perspective of Audio (table occurrence), it will only find Audio records which have Video records appearing in their portal according to your graph's filter on that relationship.  I hope that adds some understanding on the find process.

Edited September 27, 2017 by LaRetta

[dkey]

Thanks LaRetta for your time and suggestions which I will try right now

I will explain a bit further the need for those two related tables and so on.

I need to synchronise Audio which was recorded on a separate machine from the Camera

And explain why I hope to be able to order the Vidoe Audio combo and export them to another tab file. 

I would be able with a script I still need to think about ot open the files in Quicktime and rename them according to various criteria for the rest of the process. In Filemaker I did amazing stuff including video editing just using the scripts and I hope it will help me again.

The problem is:

the Audio Recorder calls each file it records using the recording date as such:(example) 170704124718.wav where the first 2 are the year, the 3-4 are the months the 5-6 are the day and the following six are the hour minutes and seconds

The Camera instead gives conventional serial numbers with the mov suffix. I am ahowever able to collect the recording date with some video editing software however as the machines are different and the people handling them are also different there are no possible ways to find fields which allow me to match the 2 database 

As the Camera also has a lower quality sound I could do it by hand (or by ear) but this is driving me nuts.

Also at times either one of the two machines will cut for various reasons, or start awhile before the other or not start at all.

This also gives me a different number of records which I can easily work with as if anything is less then a certain number of seconds I can assume is a mistake, and FMP helps a lot. 

Nonetheless I believe my only option is to find ranges say "anything filmed and recorded" within a certain amount of time in this case I have a fairly usable start point.

As the Audio tracks are more than the video tracks (because sound people will stop recording if there is any sound bothering the results) I use the Audio DB as the main source. If any filming happened and enters the range of the audio file then I can organise the recods and continue the job.

Unfortunately the files are very many as we have about 3000 takes.

If any other and certainly better idea comes to you I will be very glad.

File Maker gives me almost every single piece of info I can use as duration, timestamps, size and more. But Audio and video have different weight hence the filming time seems to be the most handy way for me to relate audio and video and synch them.

I hope this long dissertation didn't bother you and thanks again for your time and care.

Regards

dkey

[LaRetta]

Relating by timestamp range seems the best.   There may be others who have worked with audio/video files in this type of situation, particularly with QuickTime; I never have.  Hopefully others have additional suggestions.

But no, the length of your post didn't bother me at all.  The more we understand, the better our suggestions.  :-)

Edited September 28, 2017 by LaRetta